How can I ORDER BY AM PM of DateTime Stamp

Question

Post reply

How can I ORDER BY AM PM of DateTime Stamp

jgray-648822

SSC Veteran

Points: 210
More actions
February 20, 2008 at 1:32 pm

#186841

I have a stored procedure that is gathering data for a report I am running. I have data stored in a DateTime column of the database. I would like to be able to return the data based on the Date and Time portion of the stored procedure. My query looks like this and I would like to be able to have it correctly order the time portion and not see 12:20:12 AM as coming after 10:29:20 PM.
Select Location, CONVERT(CHAR(10), datetimestamp, 101) as "Date",
substring(CONVERT(CHAR(19),datetimestamp),12,10) as "Time"
ORDER BY Location, CONVERT(CHAR(10), datetimestamp, 101),
substring(CONVERT(CHAR(19),datetimestamp),12,10)
This way returns values like this:
Location Date Time
Home 2/20/2008 1:27:38PM
Home 2/20/2008 10:12:02PM
Home 2/20/2008 12:28:20AM
I would like to return these in the correct order based on the Time and AM or PM.
Any thoughts?

Viewing 4 posts - 1 through 4 (of 4 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

don't sort by the converted dates. Sort by the "raw" date:

Select Location,

CONVERT(CHAR(10), datetimestamp, 101) as "Date",

substring(CONVERT(CHAR(19),datetimestamp),12,10) as "Time"

ORDER BY Location, datetimestamp

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

You're ordering by the string conversion of a date, not the date time itself. Therefore 10:24 PM comes before 6:45 AM. (because 1 comes before 6)

If datetimestamp is a datetime in SQL (which it should be), try this.

Select Location, CONVERT(CHAR(10), datetimestamp, 101) as "Date",

CONVERT(CHAR(10),datetimestamp,108) as "Time" -- format 108 gives hh:mm:ss

ORDER BY Location, datetimestamp

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jgray-648822 SSC Veteran Points: 210 More actions · Answer 3

Thanks for all your help. That does it. I definitely over complicated this one.

Thanks again 😛