AM/PM time from Date

Posted on 2007-08-02
Last Modified: 2012-08-13
I am capturing the hour from a date field.  I want to be able to sort by hour.  I think to get this, I need AM/PM for the hour.  This is what I currently have: datepart(hh,t.Time).  This returns 20,21,22,23,0,1,2,3.  I think I want 8:00 PM, 9:00 PM, 10:00 PM, 11:00 PM, 12:00 AM, 1:00 AM, 2:00 AM, 3:00 AM.  This will sort correctly, right?  

Question by:Travidia
    LVL 5

    Expert Comment

    If you use datepart the output will always be an integer (there is no way around).  Sorting by the integer will give you the hours sorted correctly just in military time.

    If you want to see the AM PM you would need to use the RIght(item,# of string I want to see) function.

    Author Comment

    "If you want to see the AM PM you would need to use the RIght(item,# of string I want to see) function."

    What is this function?  How do I organize it in my query?
    LVL 5

    Expert Comment

    Try running this so you can get an example of the outputs I was mentioning:
    select CAST( time AS FLOAT ) thedate,time,right(time,7) from t order by thedate

    The first item is a numeric rep. of the decimal part of the date
    the second is your plane date
    the third is the time with AM/PM stamp.

    I am pretty sure you won't be able to sort the items the way you want with either of these methods.  Instead I would do a modification on what you initially did:
    select datepart(hh,time) hour,datepart(mi,time) minute,
    right(time,2) AMPM,time,right(time,7)
     from t  order by hour,minute,ampm

    There you are extracting the hour, minute and ampm and then sorting in that order.  I included the whole date and the time portion as well so you can see it is sorting correctly

    Author Comment

    I can't get that to work.  Can you show me how to extract the date and time only showing the hour?  
    Like this: 8/1/2007 10:00 PM with the minutes as 00?
    LVL 5

    Accepted Solution

    select cast(datepart(mm,mydate)as varchar(2)) + '/'+ cast(datepart(dd,logdate) as varchar(2))
     + '/' +cast(datepart(yy,logdate) as varchar(4)) +' ' +
    cast(datepart(hh,logdate)  as varchar(2))+ right(logdate,2) thedate,logdate
     from mytable  

    change all mydate to your date column and mytable to your table
    LVL 1

    Expert Comment

    create function convertTimeTo12Hr
    	@time datetime
    returns varchar(100)
    /*declare @time as datetime
    set @time = '1/1/2011 16:01:00'*/
    declare @hour varchar(5) = DATEPART(HOUR, @time)
    declare @minute varchar(2) = datepart(Minute, @time)
    declare @ampm varchar(2) = 'AM'
    if DATEPART(HOUR, @time) > 12
    	set @hour = @hour - 12
    	set @ampm = 'PM'
    if @minute < 10
    	set @minute = '0' + @minute
    --print @hour + ':' + @minute + ' ' + @ampm
    return @hour + ':' + @minute + ' ' + @ampm

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now