Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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       |
===========================
share|improve this question
    
What are you doing with the data once you have it? Where is it going? –  Mr.Brownstone Sep 20 '12 at 8:09
    
@Mr.Brownstone, Actually, I need to display the data to the end-user, which I believe that your solution is the one that I am looking for. However, I also believe that there might be a scenario whereby dba might copy one table to another. Therefore, such CONVERT() might need to be implement on the other table. –  Jack Sep 20 '12 at 9:09

4 Answers 4

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
share|improve this answer
    
+1 Used your solution in my answer. –  Jack Sep 21 '12 at 5:56

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

share|improve this answer
    
Hi Mr.Brownstone, I believe that the convert should be CONVERT(NVARCHAR, EXPIRE_TIME, 108) rather than CONVERT(EXPIRE_TIME, NVARCHAR, 108) for MSSQL 2008. (I have modify your answer) –  Jack Sep 20 '12 at 9:06
    
+1 Used your solution in my answer. –  Jack Sep 21 '12 at 5:55

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

share|improve this answer
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.