Need to show HH and "AM" or "PM'

Question

Post reply

Need to show HH and "AM" or "PM'

latingntlman

SSCarpal Tunnel

Points: 4348
More actions
November 3, 2009 at 11:44 am

#216963

I've got a datetime field for call date but I need to group my data by the hour of that call date and display it grouped and show that date time value as "HH AM" or "HH PM".
Is there such a syntax or code avail somewhere that I can use?
thx,
John

Viewing 6 posts - 1 through 5 (of 5 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would you mind sharing some sample data together with your expected result?

Please see the first link in my signature on how to post sample data.

There are several ways to do it, e.g. using dateadd() and datediff() to "normalize" your data on an hourly base.

One comment regarding the AM/PM format:

usually, date formatting should be done at the front end level. What's the reason for doing it at the database side?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323504 More actions · Answer 2

here's one way to do it, i'm sure you'll get other examples to choose from:

select CONVERT(varchar,datepart(hour,getdate()) % 12 ) + ' ' + CASE WHEN datepart(hour,getdate()) > =12 THEN 'PM' ELSE 'AM' END

--preceeding zero for HH?

select RIGHT('00' + CONVERT(varchar,datepart(hour,getdate()) % 12 ),2) + ' ' + CASE WHEN datepart(hour,getdate()) > =12 THEN 'PM' ELSE 'AM' END

--results: 2 PM

-- 02 PM

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

diamondgm SSCrazy Points: 2824 More actions · Answer 3

Another interesting way (don't know if I would call it a "good" way):

SELECT LEFT(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7),2) + RIGHT(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7),2)

-- BASED ON:

Select LTRIM(RIGHT(CONVERT(CHAR(19),GETDATE(),100),7))

edit:

I say "don't know if I would call it a "good" way" because I don't know if it will perform well. I'm sure other will suggest more efficient ways.

latingntlman SSCarpal Tunnel Points: 4348 More actions · Answer 4

I ended up using the code below and it works for what I'm trying to do. Thanks for your input and I've saved it as well.

Case When [Hour] > 12 Then convert(varchar(2),[Hour]-12) + ' PM' When [Hour] = 12 Then convert(varchar(2),[Hour]) + ' PM' When [Hour] < 12 Then convert(varchar(2),[Hour]) + ' AM' End,

In a subquery I'm pulling the field [Hour] as:

[Hour]= datepart(hh,a.CallDateEnded),

Regards,

John

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Could you post the full query you are using? There may be a better way do accomplish the task.