Convert Datetime to time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert Datetime to time

Author

Topic

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2010-08-24 : 06:03:19

I am retrieving some columns from my table and would like a datetime field to be formatted from 2010-01-01 00:00:00:000 to 00:00. My current SQL code is -

SELECT     
Col1,
Col2,
Col3,
Convert (Datetime, Col4, 108) As NewTimeOnlyFormat,
col5
    
FROM         
Table
    
WHERE 
(Col1=@Col1)

However everything runs fine without formatting the column to display just the time?

Could anyone advise why not and how to resolve please?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-24 : 06:16:20

Convert (Datetime, Col4, 108)

should be

Convert (char(10), Col4, 108)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-24 : 06:18:32

[code]CAST(Col4 AS TIME(0)) AS NewTimeOnlyFormat,[/code]

Also see this sample code[code]declare @t datetime = getdate()

SELECT @t, cast(@t as time(0))[/code]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-24 : 06:19:37

If you want only hours and minutes,

CONVERT(CHAR(5), Col4, 8) AS NewTimeOnlyFormat,

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources