Share via

SSRS Sort by Format AM vs. PM

SR[CA] 20 Reputation points
2023-04-13T21:09:45.9366667+00:00

Hi, I am looking to sort my report based on a varchar column of AM/PM. (example below) .
I am using Microsoft Visual Studio 2019/ Version 16. Example of current column sort: 1 PM 10 AM 12 PM 2 PM 9 AM 3 AM I would like to sort like below: 3 AM 9 AM 10 AM 12 PM 1 PM 2 PM thank you, SR

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,878 questions
Accepted answer
  1. AniyaTang-MSFT 12,421 Reputation points Microsoft Vendor
    2023-04-14T01:34:44.4566667+00:00

    Hi @SR[CA] You can use the CDate function to convert a value to a date and then use it when sorting. I did the test. 1

    Preview: 2

    If the answer is helpful, please click Accept Answer and Up-Vote for the same. If you have any questions, please feel free to let me know. Best regards, Aniya

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 107.1K Reputation points
    2023-04-13T21:21:03.0866667+00:00

    Living in a part of the world where everything is in 24-hour format, I can't but sigh. Why do people have to make things difficult for themselves? Anyway, try:

    ORDER BY right(col, 2), try_cast(left(col, charindex(' ', col) -1) AS int)
  2. Ken Wenze 0 Reputation points
    2023-04-13T23:15:06.0433333+00:00

    =right(Fields!HOUR12_LONG.Value,2) MS_QA_Sort.jpg

    0 comments