Post reply

Displaying time in 12 hour format

  • December 13, 2010 at 5:07 am

    #388426

    Hi All,

    I need to display time in 12 hour format.

    I got a solution for this as :

    select Ltrim(right(convert(char(19),getdate(),100),7))

    This gives time like 11:05AM , 12:08 PM etc.

    I want to use these time values for a graph. so, i need only 24 values:

    12 AM - 11PM

    i.e : 12 AM , 1AM ,2AM,3AM..........11PM ----> total 24 values.

    Can anyone help on this?

  • December 13, 2010 at 5:29 am

    #1261630

    How about this ?

    SELECT CASE WHEN DATEPART( HH , GETDATE()) > 12 THEN CAST ( ( DATEPART( HH , GETDATE()) % 12 ) AS VARCHAR) + ' PM'

    ELSE CAST ( ( DATEPART( HH , GETDATE()) ) AS VARCHAR) + 'AM'

    END AS HourParam

  • December 13, 2010 at 5:32 am

    #1261631

    there is a technique for using dateadd/datediff together to get the starting date/time for any given period..year,month,week, even hour.

    here's an example, giving you the begin hour and that same hour + 1: the rest for getting the time formatted is exactly what you posted...just substituting the equation for your getdate() in your example.

    hope this helps!

    /*--results

    (No column name)(No column name)(No column name)(No column name)

    2010-12-13 07:00:00.0002010-12-13 08:00:00.0007:00AM8:00AM

    */

    --beginning of the current hour

    select DATEADD(hh, DATEDIFF(hh,0,getdate()), 0),

    DATEADD(hh, DATEDIFF(hh,0,getdate()) + 1, 0), --adding one hour to or previous value

    Ltrim(right(convert(char(19),DATEADD(hh, DATEDIFF(hh,0,getdate()) , 0),100),7)), --using your same trim/convert to get the hours you are after

    Ltrim(right(convert(char(19),DATEADD(hh, DATEDIFF(hh,0,getdate()) + 1, 0),100),7))

    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!

  • December 13, 2010 at 5:59 am

    #1261641

    Hi ColdCoffee,

    Thanks a lot for your reply. This works fantastically.

    I had tried this in a much harder way. Thanks again!!!

  • December 13, 2010 at 6:00 am

    #1261642

    hi Lowell,

    Thanks for your reply.

  • December 13, 2010 at 6:06 am

    #1261646

    malavika.ramanathan (12/13/2010)

    Hi ColdCoffee,

    Thanks a lot for your reply. This works fantastically.

    I had tried this in a much harder way. Thanks again!!!

    Thanks for the feedback , Malavika.. one caveat though, when the time is between 12 AM and 1 AM, the code will show as 0 AM.. to counter that, u can add another WHEN clause !

  • December 13, 2010 at 6:07 am

    #1261648

    Ya i have added it!

    Thanks 🙂

  • December 20, 2010 at 2:21 am

    #1264528

    Hi,

    Though i added another WHEN clause, i had a problem. I am still getting 0 AM.

    Can anyone please help?

  • December 20, 2010 at 3:06 am

    #1264537

    Sorry i was not very clear.

    I am getting 12 AM TWICE if i try to use a case to change the 0 to 12.

    the query i am using now is :

    SELECT COUNT(id) as SessionCount

    ,CASE WHEN DATEPART( HH , timeuploaded) = 0 OR DATEPART( HH , timeuploaded) = 12 then '12 AM'

    WHEN DATEPART( HH , timeuploaded) > 12 THEN CAST ( ( DATEPART( HH , timeuploaded) % 12 ) AS VARCHAR) + ' PM'

    ELSE CAST ( ( DATEPART( HH , timeuploaded) ) AS VARCHAR) + 'AM'

    END AS HourParam

    FROM telemetry

    but i want to do something which gives me only 24values: 12AM to 11PM.

    Can someone help?

  • December 20, 2010 at 7:39 am

    #1264628

    malavika.ramanathan (12/20/2010)

    Sorry i was not very clear.

    I am getting 12 AM TWICE if i try to use a case to change the 0 to 12.

    the query i am using now is :

    SELECT COUNT(id) as SessionCount

    ,CASE WHEN DATEPART( HH , timeuploaded) = 0 OR DATEPART( HH , timeuploaded) = 12 then '12 AM'

    WHEN DATEPART( HH , timeuploaded) > 12 THEN CAST ( ( DATEPART( HH , timeuploaded) % 12 ) AS VARCHAR) + ' PM'

    ELSE CAST ( ( DATEPART( HH , timeuploaded) ) AS VARCHAR) + 'AM'

    END AS HourParam

    FROM telemetry

    but i want to do something which gives me only 24values: 12AM to 11PM.

    Can someone help?

    It is because of your first WHEN: 12:20AM and 12:20PM will give you the same result as: 12AM.

  • December 20, 2010 at 7:53 am

    #1264636

    Try this:

    SELECT COUNT(id) as SessionCount

    ,CASE WHEN DATEPART(HH, convert(varchar,DATEADD(hh, DATEDIFF(hh,0,timeuploaded), 0),120)) = 0 then '12 AM'

    WHEN DATEPART(HH, convert(varchar,DATEADD(hh, DATEDIFF(hh,0,timeuploaded), 0),120)) > 12 THEN CAST ( ( DATEPART( HH , timeuploaded) % 12 ) AS VARCHAR) + ' PM'

    ELSE CAST ( ( DATEPART( HH , timeuploaded) ) AS VARCHAR) + 'AM'

    END AS HourParam

    FROM telemetry

Viewing 11 posts - 1 through 10 (of 10 total)

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