Skip to Content
0

Crystal Reports 2013 - Converting a Number into a Date:

Feb 01, 2017 at 02:44 PM

132

avatar image

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)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Ian Waterman Feb 06, 2017 at 09:48 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi Ian,

That worked!

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

Thank you.

Gareth

0

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

1
Don Williams
Feb 01, 2017 at 02:48 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Gareth Davies Feb 17, 2017 at 08:58 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded