Solved

# date time formula  from 12/31/1840

Posted on 2011-05-11
822 Views
Trying to create a formula that converts a date greater that 12/31/1840 in days and seconds I believe

example:
field 61478,36335
so I believe this is 61478 days, 36335 seconds past the date 12/31/1840.
does anyone know the syntax that will create a date time formula  in crystal?
0
Question by:shockacon

LVL 100

Expert Comment

What system is giving you that number?

Is it anumber or string?

Unix stores seconds since 1 Jan 1970
See

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_20960792.html

http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_23383348.html

If it is as you say then
DateAdd('s', 61478 *  86400 + 36335 , DateTime(1840,12,31,23,59,59))

mlmcc
0

Author Comment

Sorry it's a string just as it's listed above. Do I need to parse it out I'm guessing?
0

LVL 100

Expert Comment

Yes you would need to parse it

Local StringVar Array strDateParts;
Local NumberVar nbrDays;
Local numberVar nbrSeconds;

strDateParts := Split ({YourField},",");
nbrDays := Val(strDateParts[1]);
nbrSeconds := Val(strDateParts[2]);

DateAdd('s', nbrDays *  86400 + nbrSeconds , DateTime(1840,12,31,23,59,59))

mlmcc

0

Author Comment

Ok I'll try it out.  Does all of this go in the formula or the top part go in the header?
0

LVL 100

Expert Comment

That is all 1 formula

mlmcc
0

Author Comment

ok well didnt quite work..
example the string  61478,32758
brought back a date of 3/22/1873   2:37:41AM
Its close but not quite.. any ideas?

using this
Local StringVar Array strDateParts;
Local NumberVar nbrDays;
Local numberVar nbrSeconds;

strDateParts := Split ({VALUE},",");
nbrDays := Val(strDateParts[1]);
nbrSeconds := Val(strDateParts[2]);

DateAdd('s', nbrDays *  86400 + nbrSeconds , DateTime(1840,12,31,23,59,59))
0

LVL 100

Expert Comment

What should the value be?

You may have to adjust for daylight savings time.

The date may also be saved as the date in GMT so you have to adjust for you location.  For instance, New York is GMT - 5 hours (4 for DST)

mlmcc
0

Author Comment

Well like u was saying this is what it's returning for one record

example the string Â 61478,32758
brought back a date of 3/22/1873 Â  2:37:41AM

Which is off by 125 years or so. :) so it's doing something
Just not quite right.
0

LVL 100

Expert Comment

What should the date be?

Most systems base the date from 1 Jan 1970.

Do you have other sample fields and the correct date?

mlmcc
0

Author Comment

It's a wierd system. As far as I understand it it's the number of days and seconds  since 12/31/1840. I don't have and example of a correct date currently.
If u take 61478 / 365 then add to year 1840 it's close.
0

LVL 100

Accepted Solution

Try this formula  I bet the 61478 * 86400 causes an overflow

Local StringVar Array strDateParts;
Local NumberVar nbrDays;
Local numberVar nbrSeconds;

strDateParts := Split ({VALUE},",");
nbrDays := Val(strDateParts[1]);
nbrSeconds := Val(strDateParts[2]);

mlmcc
0

Author Comment

ok this
Local StringVar Array strDateParts;
Local NumberVar nbrDays;
Local numberVar nbrSeconds;

strDateParts := Split ({VALUE},",");
nbrDays := Val(strDateParts[1]);
nbrSeconds := Val(strDateParts[2]);

brings back dates in 2009 I need to check the front end and see if its the same

Ieft off  the first line
as it didnt like it before the declarations
0

LVL 34

Expert Comment

FWIW, mlmcc was correct.  The days * seconds was overflowing.  I just did some experimenting (using 01/01/1840 as the base date) and that count argument to DateAdd is apparently a signed 32 bit integer, limited to around 2^31, or 2,147,483,648.  24855 * 86400 is just under that and seems to work fine.  Any values over that and you start getting incorrect results, starting back in 1771 (because the value is being interpreted as negative).

James
0

Author Comment

ok so do I need to add ths line back in. .?

If so where ?
Again it's causing the formula to fail.
Thx
0

LVL 34

Expert Comment

mlmcc can say for sure, but I imagine that that was just a leftover from some testing he was doing or something.  You certainly don't need it as is, since it's not setting any variables or anything, and I don't think it's supposed to be there at all.  Does the formula seem to be working OK without it?

James
0

LVL 100

Assisted Solution

Actually I started to give the single line as a test then gave him  the formula using his fields.  I forgot to put any comments between.

mlmcc
0

Author Closing Comment

Thanks!!
0

## Featured Post

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 biâ€¦
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!