sorting times

[maxf]

I have a column,PLAY_TIME, in my database which Im using to hold the time, hour, of a scheduled event. For example, PLAY_TIME, holds the values, 9:00AM, 9:15AM 10:00AM etc...
The PLAY_TIME column was set up as a VARCHAR data type, by the previous developer.

The problem is that when I try to sort this data in my sql statement, ORDER BY PLAY_TIME , the sort order is incorrect. Because the column type is VARCHAR, the items are being interpreted the the value of their first character, so its showing 10:00 AM before 9:00AM as 1 is less than 9.

How can I sort the times approriately? I know that if the column type is converted to a DATETIME data type, this would sort properly, however, the problem then is that the data is inserted into the column and I simply need times (hours) stored in the column.

Can anyone suggest a solution, hopefully quick and easy.

thanks for any help

 

[AWithers]

Havent tested it but you should be able to sort the data using a convert in the order by clause

e.g. select * from yourtable
order by convert(datetime,yourtimefield)

this way you dont change the field type simply use the converted values as the sort criteria

You may need to fiddle with the convert function to get it to work correctly see BOL

Andy

 

[maxf]

I get this error when trying to perform the Convert using this code, convert(datetime, s.play_time):

Microsoft OLE DB Provider for SQL Server error '80040e07'

Syntax error converting datetime from character string.

/stage/cadmin/add_tee_time.asp, line 60


Any idea how I can do the convert succesfully? I dont understand why they have a convert function that wont let you convert to a certain data type.

thanks for your help

 

[sunila7]

Hi,

Try this SQL


select * from TBL
order by (convert(decimal(10,2),LEFT(TimeFLD,LEN(TimeFLD)-2))+ (CASE charindex('AM',TimeFld) When 0
Then 12 else 0 End)) desc


Hope it helps........

Sunil

 

[puredesi5]

You can also prefix single digits (1-9) with 0. So it would be 09:30 instead of 9:30. Then it would sort correctly.

 

[maxf]

I changed the column data type to datetime so it sorts now. I do have a different question now. In my table I have a column for PLAY_DATE (smalldatetime) and a column for PLAY_TIME (datetime). I want to order records based on PLAY_DATE, on if there are multiple records on the same PLAY_DATE, then on PLAY_TIME. So for example, the following dates should be ordered like this:

1/15/2003 9:00 AM
1/15/2003 10:00 AM
1/16/2003 11:00 AM

Right now, I am using the SQL syntax:
"Order By play_date,play_time desc"
but this is only ordering by play_date.

How can I order by date, then time? Would I need to join the two columns somehow with an alias, then order by the alias?

Again, thanks for your help