Convert UNIX time stamp from number to date in Crystal Reports

[Former Member]

I have a field in my MySQL database that saves the date in the form of a Unix Time Stamp (int type data field).

I am trying to display this in my Crystal Reports by using a formula to convert the seconds to a date value. I follow this solution but it ends up giving me an error stating : The remaining text does not appear to be part of the formula.

Here is my formula:

{@ConvertUTS}
DateAdd("s",{order1.OrderDate},DateValue(1970,1,1))

The OrderDate field consists of the numeric value of the date in seconds.

Am I missing out on something here based on the Crystal Syntax?

[former_member292966 · Accepted solution]

Hi Bhavin,

Make sure Crystal sees your OrderDate field as a number. Also, make sure that field does not contain nulls.

You can convert OrderDate to a number like:

If Not IsNull ({order1.OrderDate}) Then 
     DateAdd("s",ToNumber ({order1.OrderDate}),DateValue(1970,1,1)) 
else Date (0, 0, 0); 

The Date (0, 0, 0) is a NULL date.

Good luck,

Brian

[vitaly_izmaylov]

The formula looks correct.

If you want to test the field value, then you can replace the field with hardcoded number like:

DateAdd("s",100000,DateValue(1970,1,1))

It should return: 1/2/1970 3:46:40 AM