Follow

Why are my Dates off in Reports?

When you create a DUI field using a date time picker, the value is stored in the database as Universal time or UTC. When you place the data field into a report, you'll see the time as it's stored in the database. You need to convert it to your own timezone.

Interface Display Versus Database

?name=media_1438871045747.png

Above is an example where the DUI field has a date time picker. It accurately displays the date and time selected but you can see that the value in the database appears off.

Value in a Crystal Report

?name=media_1438871144206.png

In the example above, the left value is from the database. To the right is the value corrected using a formula.

Date Time Correction Formula in Crystal Reports

?name=media_1438871214388.png

In Crystal Reports, create a new formula for the corrected value.

Use the formula below replacing the example date time field with your field.

Insert the formula into your report.

if IsDateTime ({Case_153_Extension.DNAIEDatePicker_Id_43161}) then
ShiftDateTime (DateTimeValue ({Case_153_Extension.DNAIEDatePicker_Id_43161}), "UTC,0","" )
else
DateTimeValue (1900, 01,01 )

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk