order britsh date format SQL / SSRS

Question

I have the following query in which I am trying to order dates in British format using SQL in a SSRS package for a parameter

SELECT DISTINCT  convert(varchar, date, 103) AS  Date
,convert(varchar, date, 103) AS Date Name


  FROM [Server].[dbo].[date_Table] 

  WHERE ([date] >= '01-Jan-2018' AND [date]  <= CURRENT_TIMESTAMP)


union 
select '0', 'All Dates'

order by convert(varchar, date, 103)asc

Column [date] is of type datetime.

All the dates appear not to order by correctly.

Any ideas team why?

please help

Answer 1

Format is one thing and order is another. You can format a date in any format you like but for ordering, it just makes no sense to "order in British format".

I guess that the DISTINCT and conversion to format 103 is used to get only the distict dates with the actual values of the [date] column have a time part as well. In that case, I suggest you cast to DATE type and use GROUP BY instead:

SELECT
    convert(varchar, CAST(d.[date] AS date), 103)
        AS date_in_british_format
FROM
    [Server].[dbo].[date_Table] AS d
WHERE
      d.[date] >= '2018-01-01'
  AND d.[date] <= CURRENT_TIMESTAMP
GROUP BY
    CAST(d.[date] AS date)
ORDER BY
    CAST(d.[date] AS date) ;