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


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


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


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","" )
DateTimeValue (1900, 01,01 )

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


Please sign in to leave a comment.
Powered by Zendesk