SSRS Sort by Date goes from 31/01/2018 to 01/01/2019 instead of 01/02/2018

(0) Share

Report

Posted on by Community Member

Hello friends,

I've created a report for our events team that organises all their service calendar bookings by date for the whole year. The table is currently sorting on Scheduledstart field which returns as mm/dd/yyyy hh:mm, such as 1/23/2018 14:00, which up til now has been fine (i changed the number format of the column to just be date in the UK format, 23/1/2018.

However, now that they are putting in bookings for 2019, the sorting jumps from 31/01/2018 to showing bookings from 01/01/2019. It's as though it doesn't recognise it as a date/time that is a year later and rather one number/character increased. I'm not sure how to get this value to operate as a date, perhaps something in the SQL command for the dataset?

The question is how do we get this show chronologically and go to February 2018, only going to January 2019 after the end of December 2018?

Many thanks in advance!

*This post is locked for comments

I have the same question (0)

All responses (3)

Answers (0)

David Jennaway 14,065 on at

Like (1)

Report
Is this a SQL or Fetch-based report ? If it's a Fetch-based report, you'll get 2 fields that represent each DateTime field - for Scheduledstart you'd get:

Scheduledstart - this will be a text field

ScheduledstartValue - this will be a datetime field

To get the sorting you want, you'll need to sort by the datetime field - i.e. ScheduledstartValue

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi David,

Thanks for chipping in! This is a SQL pulled dataset, is it possible to get this functionality with SQL?

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

If your data is in string format, you can use the CONVERT function in SQL to convert into from a string value into a numeric value. You can also convert it within your SSRS report as a calculate field.

You can use something like this to sort:

SELECT new_invoicedate FROM Filterednew_Invoice

ORDER BY convert(nvarchar(10), new_invoicedate, 111) ASC

Take a look at the following link for formats that you can use for sorting and decide which one will work for you:

www.sqlinfo.net/.../sql_server_SELECT-Formatting_Date_Time.php

Hope this helps.

Was this reply helpful? Yes No