Skip to Content
avatar image
Former Member

Convert UNIX time stamp from number to date in Crystal Reports

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?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 24, 2017 at 12:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 23, 2017 at 05:01 PM

    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
    Add comment
    10|10000 characters needed characters exceeded