Displaying times as am/pm - 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)

Displaying times as am/pm

Author

Topic

malletts
Starting Member

16 Posts

Posted - 2003-03-21 : 10:55:37

Hi,

Can anyone advise how I would display times in the format of
10:00am, 10:00pm etc. Currently I am using the statement below and this is returning me times in 24hr format such 17:00. I need to format this as am/pm and in 12hr clock

select CONVERT(char(5), interview_time, 114) from tblapplicant

All help gratefully received.

Thanks,
Stephen

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-21 : 22:15:29

Did you try using Convert() with the 109 format code? Just shave off the parts you don't want with Stuff:

SELECT Stuff(Stuff(Convert(varchar(26), interview_time, 109), 1, 12, ''), 5, 7, '')

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-22 : 22:12:58

Or (almost copy of above)

select right('0'+ltrim(right(convert(varchar(19),interview_time,100),7)),7)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

malletts
Starting Member

16 Posts

Posted - 2003-03-24 : 05:10:09

Thanks for the help - works fine! Can you help with another small matter?

Trying to display full month in the form of Tuesday 12th March 2003 using the following statement:

select LEFT(DATENAME(dw,interview_date),9)+ ' '+CONVERT(char(12), interview_date, 113) from tblapplicant

Thanks again

Hi,

Can anyone advise how I would display times in the format of
10:00am, 10:00pm etc. Currently I am using the statement below and this is returning me times in 24hr format such 17:00. I need to format this as am/pm and in 12hr clock

select CONVERT(char(5), interview_time, 114) from tblapplicant

All help gratefully received.

Thanks,
Stephen

[/quote]

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2003-03-26 : 22:51:19

SELECT Datename(dw,interview_time) + ' ' + (CASE datepart(d,interview_time)
WHEN 1 THEN CAST(datepart(d,interview_time)AS CHAR(2)) + 'st'
WHEN 2 THEN CAST(datepart(d,interview_time)AS CHAR(2)) + 'nd'
WHEN 3 THEN CAST(datepart(d,interview_time)AS CHAR(2)) + 'rd'
WHEN 21 THEN CAST(datepart(d,interview_time)AS CHAR(2)) + 'st'
WHEN 22 THEN CAST(datepart(d,interview_time)AS CHAR(2)) + 'nd'
WHEN 23 THEn CAST(datepart(d,interview_time)AS CHAR(2)) + 'rd'
WHEN 31 THEN CAST(datepart(d,interview_time)AS CHAR(2)) + 'st'
ELSE CAST(datepart(d,interview_time)AS CHAR(2)) + 'th'
END)
+ ' of ' + datename(m,interview_time) + ' ' + cast(datepart(yy,interview_time) AS CHAR(4))

And heaven help if they add any days to the month!

Sarah Berger MCSD

Edited by - simondeutsch on 03/26/2003 22:52:28

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources