Numeric Time/Date field: DST and Leap Year ? 1

[JULY1]

I am currently using the following formula to convert the timedate field which is in seconds (example: 320,251,407) to a standard time and date (example: 5/1/2002 7:10:07AM).

{CallDetailRecord.dateTimeConnect} / 86400 + DateTime (1969,12,31,20,00,00)

Note: In the formula after the "1969,12,31" is a 20 for Spring, then in the Fall we have to change it to a 19.

The problem with this formula is that when running reports that cover a time period which includes a daylight savings time period the time gets off track. Not-to-mention I don't know what kind of problems will occur during a leap year. Other than that it is accurate.


Thank You,
Linda

When responding please keep in mind that I'm just a novice at this.

 

[kenhamady]

Leap years shouldn't be a problem because the conversion calendar should account for them. But you will have to write a formula to check to see if you are in the daylight savings time period. You will need something like the following, although I don't know the dates you need to use. The dates will vary year to year, since they are always on Sundays:

If DateField in
Date ( year (DateDateField) , 6,15)
to
Date ( year (DateDateField) , 9,15)
then 20
else 19 Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[JULY1]

Ken,

I have tried your suggestion and have created a formula called "DST START" and it goes like this:

If {@Start} in //@Start is my Start Date
Date (year({@Start}),4,7) //4,7 is for April 7
to
Date (year ({@Start}),10,26) //10,26 is for Oct 26
then 20 //add hour begin of DST
else 19 //subtract hour end DST

It does display a 20 or 19 in my report for the appropriate time range. However, it doesn't override what is in my @Start (date field), and therefore the correct time is not displaying. Is this formula only a means of checking and not altering? I would like to override what is in the @Start.


Note: I'm learning CR formulas as I go along. It took me a while to understand what DateDateField was. I hope I got it right.

Thank You,
Linda

 

[kenhamady]

You use the result of this formula to create your other (@start) formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[JULY1]

I am so embarrassed to ask - but I've tried creating a formula using the two {@start} and {@DST Start}, it went like this: {@start}+{@DST Start} and it changed my Start month in my report from October to November.

How do I use the result of {@DST Start}- see formula below

If {@Start} in //@Start is my Start Date
Date (year({@Start}),4,7) //4,7 is for April 7
to
Date (year ({@Start}),10,26) //10,26 is for Oct 26
then 20 //add hour begin of DST
else 19 //subtract hour end DST

in an other (@start)formula? This is my current @start:

{CallDetailRecord.dateTimeConnect} / 86400 + DateTime (1969,12,31,20,00,00)


How do I create this other (@start)formula?

 

[kenhamady]

You can't use the Start formula in the {@DST Start} formula. You have to use the actual date field in {@DST Start}.
This is because you are going to use {@DST Start} in the Start formula. You replace the 20 with {@DST Start} like this:


{CallDetailRecord.dateTimeConnect} / 86400 + DateTime (1969,12,31,{@DST Start} ,00,00)


It will use 19 on some records, and 20 on others. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com