Get the Time from DateTime - 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

General SQL Server Forums

New to SQL Server Programming

Get the Time from DateTime

Author

Topic

archana23
Yak Posting Veteran

89 Posts

Posted - 2013-07-02 : 09:57:05

Hi,

I am using below query to get the DateTime as May 21 2013 5:33PM

select CAST(DATEADD(ss,1048440809,'1980-03-01 00:00:00.000')AS VARCHAR)

From above query results May 21 2013 5:33PM i just need only time as 17:33

How could i get this ?

Thank you.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 10:01:48

If you are on SQL 2008 or later:

select LEFT(CAST(DATEADD(ss,1048440809,'1980-03-01 00:00:00.000')AS TIME),5)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-02 : 10:21:56

Since there are only 86400 seconds on a day, try this

SELECT  CONVERT(CHAR(5), DATEADD(SECOND, 1048440809 % 86400, '19000001'), 8)

or this

SELECT  CONVERT(CHAR(5), DATEADD(SECOND, 1048440809, '19800301'), 8)

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