CODEWITHSUNDEEP
sqlsql-serverdatesql-order-byvarchar
LD16
LD16

Reputation: 95

SQL Server -Order BY

I have a field in table that is varchar(12), however data in that field is a date - 1/1/2016 (I have about 5,000 dates). I've been trying to do an ORDER BY but it doesn't seem to order by the date. How would I go about doing ORDER BY on that field if it's not a DateTIME field??

Upvotes: 0

Views: 122

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

The conversion of a date such as "1/1/2016" using CAST() depends on localization settings.

Assuming the value is in MM/DD/YYYY format (some countries do use DD/MM/YYYY), then this is more safely written as:

order by convert(datetime, field, 101)

Then, you run the risk that the conversion may fail, if a field does not exactly match this format. To prevent this error, SQL Server 2012+ offers try_convert().

So, I think the best approach is:

order by try_convert(datetime, field, 101)

Upvotes: 3

vercelli
vercelli

Reputation: 4767

Try:

Order by cast(Field as datetime)

Upvotes: 5

Related Questions