• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

How do I code a formula to have decimal number convert to a date In Crystal Reports 11?

The table in SQL houses the datetime as 20,080,403,140,400.00 and I need this to be in date format so that I can calcuate the time duration. I have a created datetime and a resolution datetime.  How best to do this?  Help!
What's the formula string or what steps should I take?
  • 2
2 Solutions
I assume that is in yyyyMMddhhmmss.ss format

SO that value should be 3 Apr 2008 2:04:00.00 pm

Try this formula.  You will need 2 versions.  1 for each date field you need to convert.

Local NumberVar intDateValue := {YourDateField};
Local NumberVar intYear;
Local NumberVar intMonth;
Local NumberVar intDay;
Local NumberVar intHour;
Local NumberVar intMinute;
Local NumberVar intSecond;

intYear := intDateValue \ 10000000000;
intDateValue := intDateValue mod 10000000000;
intMonth := intDateValue \ 100000000;
intDateValue := intDateValue mod 100000000;
intDay := intDateValue \ 1000000;
intDateValue := intDateValue mod 1000000;
intHour := intDateValue \ 10000;
intDateValue := intDateValue mod 10000;
intMinute := intDateValue \ 100;
intDateValue := intDateValue mod 100;
intSecond := intDateValue;

DateTime(intYear, intMOnth, intDay, intHour, intMinute, intSecond)

Open in new window

Assuming that mlmcc is correct about the format, and that it's consistent, you could use a formula like this for each field:

DateTime (Picture (CStr ({your field}, "#"), "xxxx/xx/xx xx:xx:xx"))

 The CStr function converts the number to a string with no decimal places (CR doesn't like decimals in a time string).
 The Picture function adds "/"s in the date and ":"s in the time, and puts a space in between the date and time.
 The DateTime function converts that string to a datetime.

gbautista34Author Commented:
Am I running this code as a SQL statement within Visual studio or is it directly in Crystal Reports?   Thanks!
We're both talking about a formula in CR.  Create a new formula in the field explorer and enter the code there.

 If you have two fields like the one you described, and you want to calculate the difference between them, you can create a formula for each field and then use the DateDiff function in another formula to calculate the difference.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now