Skip to Content
avatar image
Former Member

Crystal Reports 2013 - Converting a Number into a Date:

Hello Experts,

I'm new to Crystal Reports syntax, so forgive me if I'm making an obvious mistake.

I've pulled a database data from Manage Engine Service Desk into Crystal Reports.

Now, this Manage Engine Service Desk database barely makes use of DATE datatypes for things that would logically need a DATE datatype, such as, well...dates.

Instead it mostly uses the NUMBER datatype.

In Service Desk, dates will display as i.e. Feb 1, 2017 07:39 AM.

So that's a MM/DD/YYYY HH/MM format.

When pulled into Crystal Reports, these dates display as i.e 1485770973913 (see below image)

  1. I've created a Forumula Fields labelled 'Created Date'.
  2. Gone into the Formula Workshop.
  3. Gone to Functions > Type Conversion > CDate.
  4. Inserted CDate ({WorkOrder.CREATEDTIME}
  5. Checked for errors > No errors found.
  6. Save and close
  7. An error pops up (see below image).

Any input would be appreciated :)

Thanks for all your help,

greycommotion

capture.png (15.9 kB)
capture2.png (2.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Feb 06, 2017 at 09:48 AM

    Looks like some weird hybrid of UNIX time but using milliseconds instead of seconds from 01/01/1970

    This formula returns a date but not sure if correct

    dateadd('s', (1485770973913/1000), datevalue(1970, 01, 01))

    Replace number with your datefield

    dateadd('s', ({DateField}/1000), datevalue(1970, 01, 01))

    Ian

    Add comment
    10|10000 characters needed characters exceeded

    • I have always found the books by George Peck very good. You can get them on Amazon. I think he stopped at Crystal 2008, but not a great deal has changed since then so it will give you a good basis to work from.

      Ian

  • Feb 01, 2017 at 02:48 PM

    Hi Gareth,

    You need to convert that number into a date before using CR's date formulae, CR will not do it for you unless you tell it how to convert it.

    How does that number get turned into a date in your database or application that saves the date in that format?

    Don

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Don,

      I have asked the Service Desk forum, but there has been no reply yet - however...

      I've found an updated version of Service Desk's "Work Order" table, and it turns out that the CREATEDTIME column is a TIME datatype. Not a NUMBER - my mistake!

      As a side note: TIMESPENTONREQ (which is a DATE datatype) also exports to Crystal Reports as a NUMBER datatype.

      Thanks.

      Gareth

  • avatar image
    Former Member
    Feb 17, 2017 at 08:58 PM

    Hi Don,

    Unfortunately, at this moment I do not know.

    I will ask this question on the Service Desk forum, see if I can get an answer, and get back to you.

    Thanks

    Gareth

    Add comment
    10|10000 characters needed characters exceeded