Writing SQL statement in sqlcmd to hide the milliseconds for column type of time

Question

I have a table called testtable that have a column named expire_time of type [time]

The problem is that when I type the following in sqlcmd:

select * from testtable

It will return the following:

===================================
|   ID   |  expire_time           |
===================================
|  1     | 17:18:19:0370000       |
|  2     | 20:11:19:0680000       |
===================================

How should I parse my SQL statement such that it will display the following?

===========================
| ID   |  expire_time     |
===========================
|  1   |   17:18:19       |
|  2   |   20:11:19       |
===========================

Answer 1

You can also cast it to TIME(0):

CREATE TABLE #testtime (
    _id TINYINT,
    _time TIME
)
GO
INSERT INTO #testtime (_id, _time) VALUES
(1, '17:18:19'),
(2, '20:11:19')

SELECT *, CAST(_time AS TIME(0)) FROM #testtime

DROP TABLE #testtime

Expected result:

_id _time   (No column name)
1   17:18:19.0000000    17:18:19
2   20:11:19.0000000    20:11:19

Answer 2

If you just need to remove the nano-seconds for display purposes you could convert the time value to a string using the below which will give you the format you desire:

SELECT ID, CONVERT(NVARCHAR, EXPIRE_TIME, 108) AS EXPIRE_TIME FROM TESTTABLE;

However, if you do decide to do this and then decide you need to perform calculations on the information, you would need to cast it back to a time value; which would give you the nano-seconds again but they would be set to zeros.

Alternatively, you could handle the formatting of the data in the object that is consuming it; for instance a SSRS report (which has the Format() function) or in an ASP.NET Web Form (which has a multitude of ways to handle this). It really depends on what your ultimate goal is that you want to achieve.

I hope this helps you.

Further Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Answer 3

Another way is to subtract the milliseconds from the time

select DATEADD(ms, -DATEPART(ms, GETDATE()), GETDATE());

so in your case

SELECT ID, DATEADD(ms, -DATEPART(ms, EXPIRE_TIME), EXPIRE_TIME) AS EXPIRE_TIME FROM TESTBASE;

in SQLCMD

c:\sqlcmd -S Servername -d DBname -q "SELECT ID, DATEADD(ms, -DATEPART(ms, EXPIRE_TIME), EXPIRE_TIME) AS EXPIRE_TIME FROM TESTBASE;"

check out this POST on SO

Answer 4

To hide the milliseconds, you can use either one of the following:

Solution (Credit: Mr.Brownstone)

SELECT ID, CONVERT(NVARCHAR, expire_time, 108) AS EXPIRE_TIME FROM testtable;

or

Solution (Credit: BartekR)

SELECT *, CAST(expire_time AS TIME(0)) FROM testtable;

For more information, please refer to their answer.