Crystal Reports 2013 - Converting a Number into a Date:

[Former Member]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi Ian,

That worked!

P.S. Can you recommend any syntax tutorials for learning Crystal?

Thank you.

Gareth

[Former Member · Accepted solution]

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

[Former Member]

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

[former_member11696]

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

[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