CODEWITHSUNDEEP
sqlsql-server
Samantha Bratcher
Samantha Bratcher

Reputation: 33

Sorting SQL Query by Numbers as String

I am working on creating some reports from data that someone else created the databases for and am having trouble getting proper sorts.

This report is supposed to show the 20 most recent entries, but the data does not include a timestamp, instead it was created using two separate varchar fields, one for the date in the format MM/dd/yyyy and one for the time in the format HH:mm:ss AM/PM. This is causing issues when I try to select the most recent data.

When I use:

SELECT top 20 * FROM SignIn order by Checkin desc, Intime desc

I get the correct date, but the times are the values that start with 9:59am and go down from there alphabetically with 6am immediately after 7pm.

After doing much research I tried, among other things: 

SELECT * FROM SignIn order by Checkout desc, CONVERT(varchar(30), outtime,  101) desc

The results were the same.

I don't do much in MS SQL, I am more fluent with with MySql and have never seen any reason to have dates stored as straight strings before. I am at a loss as to getting it to sort correctly.

I have tried creating the report with the html, php, and javascript handling the sort and display instead of having SQL just access the first records, but the load time is ridiculous since it loads the full 5000 pieces of data and then sorts them and clips all but the first 20.

As much as I wish I could, I can't change the database storage at this time, so I need something that will work with the varchars and handle the am\pm distinction if at all possible.

Any help would be appreciated.

Upvotes: 3

Views: 134

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can concatenate the two columns and cast the result as datetime and use it for sorting.

SELECT top 20 * 
FROM SignIn 
ORDER BY cast(Checkout+' '+outtime as datetime) desc

Note that this assumes all the dates and times are valid. In case you may have incorrect values, use try_cast (for SQL Server versions 2012 and later)

SELECT top 20 * 
FROM SignIn 
WHERE try_cast(Checkout+' '+outtime as datetime) is not null --invalid values get filtered out
ORDER BY cast(Checkout+' '+outtime as datetime) desc

Upvotes: 4

Hadi
Hadi

Reputation: 37313

Just you have to concatenate your date time columns and convert to date like the following:

SELECT * FROM SignIn order by CONVERT(DATETIME, [dateColumn] + ' ' + [TimeColumn], 101) desc

you can learn more about datetime format in this tutorials link

Upvotes: 1

Related Questions