Sorting Times stored as nvarchar

[joshbula]

Hello,
In SQL Server 2005, I am working with a table that has a nvarchar column that stores times in the format "8:30 AM" as a string. I need to sort by the actual time, not just the characters, so for example "8:00 AM" needs to come before "12:00 PM." Unfortunately converting the field to a datetime or smalldatetime field is not an option.

I have tried the following:

SELECT CONVERT(datetime, txtStartTime) As StartTime
FROM tblFinalSchedule
ORDER BY StartTime

and

SELECT CAST(txtStartTime AS datetime) As StartTime
FROM tblFinalSchedule
ORDER BY StartTime

I have tried substituting smalldatetime for datetime and that doesn't work either. The error message is "Conversion failed when converting datetime from character string."

I've thought about trying to Group By just the "AM" and "PM" parts of the field, but the combination of single digit and double-digit hours means it won't always be in the same place, and I can't count from the end because sometimes there are a few spaces included after the AM or PM.

Any ideas are greatly appreciated.
Thanks,
Josh

 

[RiverGuy]

If you cannot convert, then you have some unconvertable data in the table--what you could consider "bad data." So you have two options:

1. Only return data which can be converted to a date. Here is an example. I've got three times, one of which cannot be converted. The first query will fail just like you've described, whereas the second query first filters out the bad times and returns only the good ones:

DECLARE @T1 TABLE (TimeCol VARCHAR(100))

INSERT INTO @T1 SELECT '8:00 AM'
INSERT INTO @T1 SELECT '9:00 AM'
INSERT INTO @T1 SELECT '10A'

SELECT CONVERT(DATETIME, TimeCol) FROM @T1  --Fails with 10A

SELECT CONVERT(DATETIME, TimeCol) FROM @T1 WHERE ISDATE(TimeCol) = 1 --Returns only the first two, does not fail

2. Tell your boss that you would rather not hack together a poor solution and the best bet would be to properly redesign the table.

 

[gmmastros]

If you want to sort the data as though it were a time, then you need to be modifying the ORder By part, not the select part.

The second problem is "Conversion failed". This means you have rows in the table that are not valid times. In the case of invalid times, how do you want the data sorted?

Take a look at this example:

Declare @Temp Table(txtStartTime nvarchar(20))

Insert Into @Temp  Values('8:00 am')
Insert Into @Temp  Values('12:00 am')
Insert Into @Temp  Values('12:00 pm')
Insert Into @Temp  Values('8:00 pm')
Insert Into @Temp  Values('Morning-ish')
Insert Into @Temp  Values('')
Insert Into @Temp  Values(NULL)

Select txtStartTime
From   @Temp
Order By Case When IsDate(txtStartTime) = 1 
            Then Convert(DateTime, txtStartTime)
            Else Convert(DateTime, [!]-1[/!])
            End, txtStartTime

Copy/paste that to a query window and run it.

Notice the -1 highlighted in red. Basically, if the data is invalid (like Morning-ish), it will appear at the beginning of the list. If you want the invalid times shown at the end, change -1 to 1.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[joshbula]

Thanks to you both for the help, I ended up using the Case function. It wouldn't work in Order By because I'm joining two tables with a union, but it worked fine in the Select Statement as an alias, then I sorted by the alias name in the Order By and it's working perfectly.

Thanks again!
..Josh