0

I am a bit rusty and would be very grateful if you could help. I am using Reporting Services. I am using T-SQL. The date_ordered field mentioned below is data type varchar.

I have a simple report that gives the date_ordered value. Unfortunately, when I ORDER BY date_ordered DESC, it sorts the date by the numerical value of the day, so 31-Oct-14 appears first and 01-Apr-13 last.

I would like the report to show most recent dates first and end with the last date ordered.

I'm sure it's very obvious, but I can't see it.

With many thanks.

Will F
  • 417
  • 2
  • 6
  • 17
  • You'll have to provide more details. What does your dataset query look like, where do you do ordering and formatting, etc. Help us reproduce your scenario so we can help out. – Jeroen Jan 28 '15 at 08:55
  • The dataset query is very simple: Select date_ordered, date dispatched, from Orders order by date_ordered. The first value to appear is 31-Oct, the second is 31-May, etc, so it is sorting by the day numerical value, as far as I can see. – Will F Jan 28 '15 at 09:02
  • Note that on SO you can *edit* your own question to add more details. This will also bump your question again (comments won't). (PS. The query you posted will *not* be enough for us to reproduce it. Put yourself in our shoes: we need more info to help. E.g. some DDL to create a repro, or at the least table structure, data types, and some sample data.) – Jeroen Jan 28 '15 at 09:18
  • Thanks for pointing that out; hopefully the amended post is slightly less amateurish. It seemed so simple I thought there may be a simple ish solution. – Will F Jan 28 '15 at 09:46

1 Answers1

0

you just have to convert your date to a datetime format in tsql. then when you sort it it works as expected.

Select CONVERT (datetime, '24-04-2012', 105 as date) as date, 
[date dispatched] from Orders order by 1

see How convert string to date T-SQL? and https://msdn.microsoft.com/en-us/library/ms187928.aspx

Community
  • 1
  • 1
busytools
  • 361
  • 1
  • 6