Writing SQL statement in sqlcmd to hide the milliseconds for column type of time 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       |
===========================
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