SQL Server -Order BY

Question

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??

Use the correct datatype and you won't have these problems. Why are you using varchar instead of date? — Martin Smith
– Martin Smith, Commented Jun 15, 2016 at 17:04
Can you provide the query you are trying? Then you will get the right answer. — Denn
– Denn, Commented Jun 15, 2016 at 17:18
Select firstName, LastName, ServiceStart, ServiceEnd from TblClients order by ServiceEnd desc. But vercelli answered it already so it's all set. thank you! — LD16
– LD16, Commented Jun 15, 2016 at 17:36

vercelli · Accepted Answer · 2016-06-15 17:03:39Z

5

Try:

Order by cast(Field as datetime)

answered Jun 15, 2016 at 17:03

vercelli

4,7772 gold badges17 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vercelli Over a year ago

@LD16 Glad it helped. Gordon Linoff has a good point. Check his answer also.

Gordon Linoff · Accepted Answer · 2016-06-15 18:38:30Z

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)

It is the silly method that SQL Server used to format dates before introducing format(). It is a magic code, documented at msdn.microsoft.com/en-us/library/ms187928.aspx.
Thankfully I only need this to query important data that is later copied. I'm not making any stored procedures or reusing it in code. thanks tho!

Collectives™ on Stack Overflow

SQL Server -Order BY

2 Answers 2

1 Comment

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Related