SSRS DateTime Parameter Format

Question

I need to create an SSRS (2017) report with date range parameters, BeginDate and EndDate.
Context: this is a report to pull names, birthdates, and deceased dates, based on the deceased date occurring between BeginDate and EndDate.

The table I am working with stores dates in a YYYYMMDD format.

Initially I tried the simple solution of setting the parameter to DateTime format. This did not work. I am assuming it is because the local language settings put the parameter format in MMDDYYYY so selecting 02172018 to 10172019 would not produce results if all the dates in the table start with 2019(mmdd).

I don't believe adjusting the language settings would not be beneficial in this instance in the long term as there will be multiple users in multiple locations running this report so I'm guessing it wouldn't work for everyone. I'm not authorized to change the language settings on my local server for this purpose either, so that's out.

If those assumptions not correct, please advise.

So then I thought maybe if I converted the dates within the report's main dataset that it might still read it, but that didn't work either:

CASE WHEN A.birthdate = '00000000'
              THEN ''
            ELSE CASE LEN(A.birthdate)
                 WHEN 8 
                  THEN CONVERT(varchar(10), CAST(A.birthdate AS datetime),21)
                 WHEN 6 
                  THEN CONVERT(varchar(10), SUBSTRING(A.birthdate,1,4)+'/'+SUBSTRING(A.birthdate,5,2),21)
                 WHEN 4 
                  THEN CONVERT(varchar(10), A.birthdate,21)
                 ELSE NULL
                  END
        END AS BirthDate
,CASE WHEN D.DECEASEDDATE = '00000000'
              THEN ''
            ELSE CASE LEN(D.DECEASEDDATE)
                 WHEN 8 
                  THEN CONVERT(varchar(10), CAST(D.DECEASEDDATE AS datetime),21)
                 WHEN 6 
                  THEN CONVERT(varchar(10), SUBSTRING(D.DECEASEDDATE,1,4)+'/'+SUBSTRING(D.DECEASEDDATE,5,2),21)
                 WHEN 4 
                  THEN CONVERT(varchar(10), D.DECEASEDDATE,21)
                 ELSE NULL
                  END
        END AS DeceasedDate

I got the following error:
"An error has occurred during report processing. Cannot read the next data row for the dataset. The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

What else can I try to get the date parameters to work?

Answer 1

What datatype are the birth and deceased dates in the database?

If they are date or datetime and you use a date picker for your report parameters you can compare them, the format they get displayed in should not matter as internally they are stored correctly.

As a test, create a new report, add a date parameter so the datepicker is shown. Then add a textbox to show the formatted parameter value. Use a format that uses words such as "19 February 2020" so there is not ambiguity. If this works then you should have no problems

You WHERE CLAUSE would just be something like

WHERE DeceasedDate BETWEEN @BeginDate AND @EndDate

EDIT Based on OP's response..

Please try the following tests and see what results you get. Assuming you get a sensible date returned, I would create a view with an additional column that converts the char(8) format dates to real date data types the based you report on that view.

DECLARE @dt char(8) = '20200201'
SELECT CAST(@dt as date)
SELECT DATEFROMPARTS(LEFT(@dt,4),SUBSTRING(@dt, 5,2),RIGHT(@dt,2))

SELECT FORMAT(CAST(@dt as date), 'dd MMMM yyyy')

The last one is there to make sure that the days and months are being correctly interpreted.

DATEFROMPARTS should always work as you are telling it which parts of the string represent each part of the date but this is slower that the first option.

Assuming DATEFROMPARTS works, your view could just be something like

SELECT *
    , DATEFROMPARTS(LEFT(Deceased,4),SUBSTRING(Deceased, 5,2),RIGHT(Deceased,2)) as DeceasedDateNew
    FROM myTable