November 3, 2009 at 11:44 am
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
November 3, 2009 at 11:52 am
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?
November 3, 2009 at 12:43 pm
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
November 4, 2009 at 12:00 am
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.
November 4, 2009 at 11:22 am
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
November 4, 2009 at 11:31 am
Could you post the full query you are using? There may be a better way do accomplish the task.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply