Display the Time

Question

Display the Time

doss.tychicus

Old Hand

Points: 360
More actions
December 16, 2008 at 1:40 pm

#197600

I need to get only the time from the database col.So I used
select CONVERT(CHAR(8),getdate(),8) just to get the time.but it always returing times like 13:00 ,14.00 ect. is there any any I can get 1.00 PM ,2.00 PM ect...
Any help greatly appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

That third argument to CONVERT ("8" in your example) controls the formatting of the date and time. Check out the Books-OnLine article "CAST and CONVERT" for a complete list of the available formats.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

faiz_ku SSC-Addicted Points: 447 More actions · Answer 2

YOU CAN CHECK THIS Select convert(varchar,getdate(),108). It will return correct time.

doss.tychicus Old Hand Points: 360 More actions · Answer 3

Nope. it still retruns 13:10:00 using the Following statement.

convert(varchar,getdate(),108).

I think I need to use substring,but I want to make sure there is no other statements in SQL

ps. One Orange Chip Points: 29422 More actions · Answer 4

I cant find of an option returning HH:MM AM(PM)

Try using 109

SELECT substring(convert(varchar,getdate(),109),13,5)+' '+right(convert(varchar, getdate(),109),2)

Pradeep Singh

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 5

I think this is what you want.

DECLARE @date DATETIME

SET @date = '2008-01-01 16:11:10'

SELECT RIGHT(CONVERT(VARCHAR,@date ,100),6)

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

doss.tychicus Old Hand Points: 360 More actions · Answer 6

doss.tychicus

Old Hand

Points: 360

December 17, 2008 at 9:22 am

#913942

Thanks. It Works !!!!

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 7

Just an FYI. If using a variable is not an option because you're trying to do a set based conversion, then you can always use a CASE statement with a Substring.

Select CASE Substring(CONVERT(CHAR(8),getdate(),8),1,2)

When '13' Then '01' + Substring(CONVERT(CHAR(8),getdate(),8),3,6) + 'P.M.'

....

Else CONVERT(CHAR(8),getdate(),8) + 'A.M.'

End as CurTime

Bulky, but it works where variables won't. Such as when pulling a specific date field from a table (StartDate or EndDate) instead of using GetDate().

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.