Mark223344


I am using the following ;

{ fn HOUR(DateTime) } and receive the following data;

0,1,2,3...etc

Is there a way to use the above statement and have the following

12:00 AM, 1:00 AM, 2:00 AM.......etc.




Re: sql query

Manivannan.D.Sekaran


I hope the following expression will help you

Code Snippet

Select Cast(Case When Datepart(Hour,getdate()) % 12 = 0 Then 12 Else Datepart(Hour,getdate()) % 12 End as varchar)

+ ':00'

+ Case When Datepart(Hour,getdate()) >= 12 Then ' PM' Else ' AM' End







Re: sql query

DaleJ

Another option:

Code Snippet

declare @t datetime

set @t = '5/1/2007'

select ltrim(right(convert(varchar(20), dateadd(mi, datepart(mi, @t)*-1, @t)), 7))

set @t = getdate()

select ltrim(right(convert(varchar(20), dateadd(mi, datepart(mi, @t)*-1, @t)), 7))







Re: sql query

hunchback

Mark223344,

Try:

select ltrim(right(convert(varchar(25), getdate(), 0), 7))

go

AMB





Re: sql query

Arnie Rowland

Very close Alejandro,

However the OP wanted a format of 'hh:mm AP'. (Notice the space between the minutes and AP indicator.)

This alteration will produce exactly what the OP requested.


select stuff( right( convert( varchar(25), getdate(), 0 ), 7 ), 6, 0, ' ' )

--------
12:23 PM






Re: sql query

DaleJ

Still very close, but not per spec.

He seemed to want the "top of the hour", so need to remove the minutes.

Not sure if the space is significant to him or not.

select ltrim(stuff(right(convert(varchar(25), dateadd(mi, datepart(mi, getdate())*-1, getdate())), 7), 6, 0, ' '))






Re: sql query

Arnie Rowland

I missed the 'on the hour' part.

But this still seems simplier...


select stuff( right( convert( varchar(25), getdate(), 0 ), 7 ), 4, 2, '00 ' )

--------
12:00 PM