Skip to Content
0

Convert UNIX time stamp from number to date in Crystal Reports

Nov 23, 2017 at 01:44 PM

43

avatar image
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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Brian Dong Nov 24, 2017 at 12:56 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Vitaly Izmaylov
Nov 23, 2017 at 05:01 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded