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

# date time formula from 12/31/1840

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
shockacon
Asked:
• 8
• 7
• 2
2 Solutions

Commented:
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 Commented:
Sorry it's a string just as it's listed above. Do I need to parse it out I'm guessing?
0

Commented:
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 Commented:
Ok I'll try it out.  Does all of this go in the formula or the top part go in the header?
0

Commented:
That is all 1 formula

mlmcc
0

Author Commented:
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

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

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

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

DateAdd('s', 32758,DateAdd('d', 61478 , DateTime(1840,12,31,23,59,59)))

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

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

DateAdd('s',  nbrSeconds , DateAdd('d',nbrDays, DateTime(1840,12,31,23,59,59)))

mlmcc
0

Author Commented:

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

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

DateAdd('s',  nbrSeconds , DateAdd('d',nbrDays, DateTime(1840,12,31,23,59,59)))

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

Ieft off  the first line
DateAdd('s', 32758,DateAdd('d', 61478 , DateTime(1840,12,31,23,59,59)))
as it didnt like it before the declarations
0

Commented:
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 Commented:
ok so do I need to add ths line back in. .?
DateAdd('s', 32758,DateAdd('d', 61478 , DateTime(1840,12,31,23,59,59)))

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

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

Commented:
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 Commented:
Thanks!!
0

## Featured Post

• 8
• 7
• 2
Tackle projects and never again get stuck behind a technical roadblock.