Sort by AM/PM

Question

Post reply

Sort by AM/PM

Marv_SQL

SSCommitted

Points: 1612
More actions
August 23, 2011 at 9:18 am

#390251

Hello SQL World,
I am having issues with sorting a time column by AM/PM.
My column has the times listed as the following:
01:00PM
02:00PM
03:00PM
04:00PM
08:00AM
09:00AM
10:00Am
I want to be able to have the column sort by AM and then PM hours. My result set should be this:
08:00AM
09:00AM
10:00AM
01:00PM
02:00PM
03:00PM
04:00PM
Any suggestions?

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

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

Neal Sivley SSCommitted Points: 1766 More actions · Answer 1

How is that data stored? datetime, time, or char datatype?

Ray M SSC-Insane Points: 21093 More actions · Answer 2

What is the datatype of your time column?

How are you displaying time?

If you are using a datetime, or time column it implicitly orders correctly.

You have to cast it to display AM/PM. Seems to me like your using a Character data type.

Lowell SSC Guru Points: 323518 More actions · Answer 3

if you don't keep your datetimes in datetime columns, you run into wierdnesses like this.

here's one way to sort varchar representations of time the way you asked: ideally, you'd simply order by the datetime column and you'd be all set.

SELECT * FROM

(SELECT '01:00PM' AS TheTime UNION ALL

SELECT '02:00PM' UNION ALL

SELECT '03:00PM' UNION ALL

SELECT '04:00PM' UNION ALL

SELECT '08:00AM' UNION ALL

SELECT '09:00AM' UNION ALL

SELECT '10:00AM'

) MyAlias

ORDER BY

CASE

WHEN CHARINDEX('A',TheTime) > 0

THEN 1

ELSE 2

END,

TheTime

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!

Marv_SQL SSCommitted Points: 1612 More actions · Answer 4

Here is the sql for my querry.

SELECT DATENAME(month, DateSvc) AS Month, DAY(DateSvc) AS day, YEAR(DateSvc) AS 'Year', COUNT(ChargeKey) AS total,

convert(char(19),datesvc,100), Right('0' + Case When DateName(hh, datesvc) = 0 Then '12'

When DateName(hh, datesvc) <= 12 Then DateName(hh, datesvc)

Else DateName(hh, DateAdd(hh, -12, datesvc)) End, 2) + ':' +

Case When DateName(n, datesvc) >= 30 Then '00' Else '00' End +

Case When DateName(hh, datesvc) < 12 Then 'AM' Else 'PM' End As [Hour]

FROM BIL_ChargeSlips

WHERE (SvcStatusLU = 2) AND (DateSvc BETWEEN '3/1/2010' AND '4/1/2010')

GROUP BY DATENAME(month, DateSvc), YEAR(DateSvc), DATEPART(m, DateSvc), DAY(DateSvc),

datesvc

ORDER BY day, hour

The column is a char.

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Another option, if you can be 100% certain you have valid time values is to sort by casting the column as a time datatype. This also assumes you are using sql 2008 (since you posted in the 2008 forums i am making that assumption).

SELECT * FROM

(SELECT '01:00PM' AS TheTime UNION ALL

SELECT '02:00PM' UNION ALL

SELECT '04:00PM' UNION ALL

SELECT '08:00AM' UNION ALL

SELECT '03:00PM' UNION ALL

SELECT '09:00AM' UNION ALL

SELECT '10:00AM'

) MyAlias

order by CAST(TheTime as time)

EDIT: I am pretty sure that Lowell's will perform better on large datasets because it doesn't have to cast. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/