Date/Time Conversions - 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 2000 Forums

Transact-SQL (2000)

Date/Time Conversions

Author

Topic

jbraun
Starting Member

10 Posts

Posted - 2003-06-03 : 12:03:39

What is the easiest way to convert a datetime data type to time only? In particular, I am looking for the format of HH:MM AM/PM. I have tried using CONVERT(varchar(10), t.StartTime, 130) which produced the correct time result, however also included the date. I need only the time. Any help would be appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 12:08:06

SELECT Convert(varchar(26),GetDate(),108)

Which is different than:

SELECT Convert(datetime,GetDate(),108)

Brett

8-)

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-06-03 : 12:09:45

Hi

What about

CONVERT(varchar(10), t.StartTime, 8)

EDIT : Dammit, ya beat me. Get back to work

Damian

Edited by - merkin on 06/03/2003 12:10:36

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 12:23:13

I would, really, but it's so boring

...no, no more mainframe, please!!!! I'll be good.

Brett

8-)

jbraun
Starting Member

10 Posts

Posted - 2003-06-03 : 13:00:53

Those are correct in the fact that they will return the time, but it is in military format. Is there a style will will give me 2:30 PM instead of 14:30?

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 13:17:41

SELECT RIGHT(Convert(varchar(26),GetDate(),109),14)

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-03 : 13:19:15

All styles can be found in the CONVERT topic in SQL Server Books Online.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 13:24:39

AND....
I get a sense that you're not goin to be a happy with the milliseconds, so:


SELECT SUBSTRING(RIGHT(Convert(varchar(26),GetDate(),109),14),1,(LEN(RIGHT(Convert(varchar(26),GetDate(),109),14))-6))
     + RIGHT(RIGHT(Convert(varchar(26),GetDate(),109),14),2)

Brett

8-)

jbraun
Starting Member

10 Posts

Posted - 2003-06-03 : 14:19:52

Brett -- You got me figured out. This is exactly what I was looking for. Sorry for being such a rookie, but thanks for your help!

JB

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-03 : 15:49:57

Hey that's what this site's here for...don't forget to try and search the forums with keywords lot of great stuff...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources