You’re offline. This is a read only version of the page.
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
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.
Hi David,
Thanks for chipping in! This is a SQL pulled dataset, is it possible to get this functionality with SQL?
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:
To get the sorting you want, you'll need to sort by the datetime field - i.e. ScheduledstartValue
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (