I have a datetime field which I want to summarise and format to YYYY mmm then use this as a SSRS parameter to filter my query.

Q1. I when I use the format option the dates I get back are sorted as text e.g.

1. 2022 Apr
2. 2022 Feb
3. 2022 Jan
4. 2022 Mar

How do I sort as per the calendar, e.g.

1. 2022 Jan
2. 2022 Feb
3. 2022 Mar
4. 2022 Apr

Q2. How do I use the values of this parameter in my WHERE clause to filter my dates. So if someone chooses "2022 Mar" I only get data where the datetime field contains March 2022.

CodePudding user response：

try_convert(date,...) will assume the 1st of the month if no day is given

Example

Declare @YourTable Table ([SomeCol] varchar(50))
Insert Into @YourTable Values 
 ('2022 Apr')
,('2022 Feb')
,('2022 Jan')
,('2022 Mar')
 
Select *
  From @YourTable
  Order By try_convert(Date,SomeCol)  

Results

SomeCol
2022 Jan
2022 Feb
2022 Mar
2022 Apr

CodePudding user response：

You can sort it out by just using 'ORDER BY'

EX:

CREATE TABLE #TEMP_DATES
(Month_Year Date)

INSERT INTO #TEMP_DATES
SELECT 
        '2022 APR',
        '2022 FEB',
        '2022 JAN'
        
SELECT * FROM #TEMP_DATES
ORDER BY Month_Year