SSRS: Far Future Date Issues For Date/Time Parameters

It's probably easiest just to show an example of problem (the quick solution is at the end).

Create a new report following the instructions here only use the following SQL;

SELECT TO_DATE(:Data_Value)
FROM DUAL

This assumes you are using an Oracle database to connect to. It's possible that this could be just an Oracle issue but it's pretty unlikely.

Once the report is completed run it with the parameter '01-JAN-2070';

2070 Parameter Passed Through

In the result you can see we have "1/1/2070 12:00:00 AM" which is exactly what you'd expect - the text value we passed through has been successfully converted to a date on the database server.

Now go into the properties of the Data_Value parameter and change the data type from Text to Date/Time;

Report Parameter Properties: Data type

Re-run the report and enter the same test date;

2070 Parameter Converted to 1970

You'll notice that rather than displaying 2070 the year has been altered in the output to 1970.

In fact if you choose any date after 01-JAN-2050 then it will automatically revert to it's 1900's equivalent (31-DEC-2049 is ok).

Now you might be thinking that this is something specific to do with the way the machine I'm running on is configured so in order to do a quick test I've opened Outlook and created an appointment for 2070 which seems to work fine;

Microsoft Outlook: 2070 Appointment

I've then done a search and confirmed the appointment has been saved for 2070 (and not 1970).

To further confirm it's an SSRS issue go back to the report and change the report title to display the parameter value;

Report Builder 3: Adding @Data_Value as the Report Title

Now run the report (with our 01-JAN-2070) parameter value;

Report Builder 3: Date Confusion

So from the look of it the Date/Time picker *isn't* the problem (otherwise the displayed parameter value would be wrong). That's actually good news as it means we can continue to use the (nice) Date/Time picker and resolve the issues with the values being passed in the code.

Go into the Query Parameters and change;

=Parameters!Data_Value.Value

To;

=Format(CDate(Parameters!Data_Value.Value), "dd-MMM-yyyy")

And now re-run the report;

Report Builder 3: Correct Date Values Displayed

This seems to indicate that the problem (for Microsoft) is due to the assigning of entered to parameter values. However we have our fix.

Read More