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?
gbautista34Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
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


mlmcc
0
James0628Commented:
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.

 James
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gbautista34Author Commented:
Am I running this code as a SQL statement within Visual studio or is it directly in Crystal Reports?   Thanks!
0
James0628Commented:
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.

 James
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.