Format 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

General SQL Server Forums

New to SQL Server Programming

Format time

Author

Topic

projecttoday
Starting Member

31 Posts

Posted - 2010-02-23 : 18:00:03

I need to create a column with the time portion of a datetime field. I'm using

CONVERT(char(10),startdt,108)

and I get

15:00:00

but what I want is

3:00 PM

I can't find the correct style. Can anyone help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-23 : 19:03:54

[code]
select right(convert(varchar(20), startdt, 100), 7)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

projecttoday
Starting Member

31 Posts

Posted - 2010-02-23 : 20:51:50

Thanks. Is there any way to put a space between the time and the PM?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-23 : 20:55:54

stuff it


select	stuff(right(convert(varchar(20), startdt, 100), 7), 6, 0, ' ')

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-24 : 02:09:46

quote:
Originally posted by projecttoday

I need to create a column with the time portion of a datetime field. I'm using

CONVERT(char(10),startdt,108)

and I get

15:00:00

but what I want is

3:00 PM

I can't find the correct style. Can anyone help?

If you use front end application, do the formation there

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 09:34:25

also see

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

projecttoday
Starting Member

31 Posts

Posted - 2010-02-25 : 17:49:23

The Stuff is working. Thank you.

This is for a listbox in Access. You've got a row source and a column width. No way to format it except in the SELECT. (?) Would be nice since the stuff/convert is a bit complicated. But I don't think there is any other way.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources