Custom report not displaying local date time offset

(0) Share

Report

Posted on by tyborg77

Hello,

I have a custom SSRS report that displays a Date field from our database. The local time is UTC+12.

The report column is set to display the date in the regional settings the report but when the report is run the values shown are one day prior to what was entered into the database. Exporting to Excel reveals the data as date time format for 12pm the previous day. My supposition is that the report is not formatting the column using the regional settings which results in a 12 hour offset.

Example: 12/5/16 stored, 11/5/16 displayed in report and when exported to Excel shows as 11/05/2016 12:00:00 p.m.

Things I have checked:

The column is formatted to display using regional settings
The Report Properties>Localization>Language is set to en-NZ
Local Users' time zone settings are (GMT+12:00) Auckland, Wellington.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

BharatPremji 2,485 on at

Like (0)

Report

Hi,

In your SSRS dataset, when you pull out the Date field, you should see 2 fields being pulled.

One field will have Value appended to the end of the field name. Try putting that on your report and see what happens

Bharat

Was this reply helpful? Yes No
tyborg77 45 on at

Like (0)

Report

That appears to already be the case the Expression is =Fields!GCL_LatestReportUpdate.Value

Was this reply helpful? Yes No
BharatPremji 2,485 on at

Like (0)

Report

Hi,

What I meant was in the fields in your dataset you should see something like this:

So in your case, something like GCL_LatestReportUpdateValue

When you drop that field on the report it should be:

=Fields!GCL_LatestReportUpdateValue.Value

Bharat

Was this reply helpful? Yes No
tyborg77 45 on at

Like (0)

Report

Thanks for the clarification. I don't see two fields. I just have one for each

Was this reply helpful? Yes No
BharatPremji 2,485 on at

Like (0)

Report

Hi,

Are you using CRM on Premise?

If, so can you query the filtered view your data is in and see what values are being held in the database?

Bharat

Was this reply helpful? Yes No
tyborg77 45 on at

Like (0)

Report

Yes we have CRM on premise. In my example listed in the question the underlying value is 2016-05-11 12:00:00.000

Was this reply helpful? Yes No
BharatPremji 2,485 on at

Like (0)

Report

Hi,

I assume you are but I'll ask anyway, are you using the filtered view to retrieve the data?

If you run the query in SQL Management Studio, you should see 2 columns being returned, 1 with the UTC value and 1 with the value converted to the time zone of the user who is logged into Management Studio (assuming its the same user as your CRM user). Can you check the date/time of the value in those columns?

Bharat

Was this reply helpful? Yes No
tyborg77 45 on at

Like (0)

Report

Hi Bharat,

Thanks for that clarification. Querying the filtered data returns 2016-05-12 00:00:00.000 but only one column of data...

Was this reply helpful? Yes No
BharatPremji 2,485 on at

Like (0)

Report

Hi,

That's really strange, all seems to be in order with that field.

Next thing to try would be put the field on the report but change the format to date/time so you can see what the time portion of the field is.

It might give you an insight to what is happening

Bharat

Was this reply helpful? Yes No