Using UTC Date Time field in a Parameter

[JULY1]

I am trying to create a Parameter using a UTC Date Time field,{CallDetailRecord.dateTimeConnect}. This is a numberic field and I would like it to appear in the Parameter as a date and not numbers so that the Parameter will allow a Date Range.

In the "Detail" area of my report I've created the following formula (@Start) for the UTC DateTime:

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

With this field in the "Detail" section I've then used "Select Expert" and placed the following:
{@Start} in Aged0To30Days.

My goal is to create a Parameter that would prompt the User to select a date range that is in the period Aged0To30Days.

I'm still learning so please break down any suggestions.

Thanks,
Linda

 

[dgillz]

Please show examples of the raw data of the UTD date field.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JULY1]

The UTC timedate field which is in seconds (example: 320,251,407) to a standard time and date (example: 5/1/2002 7:10:07AM) I am unable to seperate the date from the time.

 

[dgillz]

Please explain how 320,251,407 is transalated to a date-time value.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[IdoMillet]

Assuming you have the number of seconds since 1970,

DateDiff("s", datetime(1970,01,01), {SecondsSince1970})

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[JULY1]

I don't have a problem with using {CallDetailRecord.dateTimeConnect} in the detail section of my report with the formula listed above labeled @Start. But, I can't create a Parameter using a Formula.

My problem is that I would like to create a "Parameter" using {CallDetailRecord.dateTimeConnect}, so that the Report would prompt for a date range. But since the field {CallDetailRecord.dateTimeConnect} is a numeric value I can't set-up the Parameter with a value type of "Date", because the value type of this field is "Number". This means that when it prompts for a Range Value, all that will appear is a list of nine (9) digit numbers (see earlier example).

 

[dgillz]

Correct, you cannot create a parameter using a formula. However you can create a formula using a parameter. Create a record selection formula like this:


{YourDateField} = {?YourDateParameter}

This assumes you can convert the value you provided in the example above to a date, and replace {YourDateField} with it.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[JULY1]

I've tried it but it won't convert the value into a date, the parameter is looking for a digit.

The {CallDetailRecord.dateTimeConnect} field is a Date and Time value which represents the number of seconds since Midnight (00:00:00) January 1, 1970.

I am using Crystal Reports 8 Professional and there is nothing to convert this into a DateTime field.

Any other suggestions?

 

[lbass]

Try creating a formula in the formula editor {@datetime}:

dateadd("s",{CallDetailRecord.dateTimeConnect},date(1970,01,01))

Then use the formula in your record selection formula:

{@datetime} in {?daterange}

-LB

 

[JULY1]

I did as you suggested but it didn't work. When it looks at the parameter in the record selection formula it prompts stating "a date-time is required here".

Besides that when I tested the formula as indicated with the date(1970,01,01) it threw my date and time off. The date cannot be seperated from the time.

I've come up with an alternative approach since I'm unable to figure out how to use a parameter for a date range. I've created a datetime formula which I group by and placed in the record selection formula in Aged0To30Days. By doing this the User can select the desired week from the group tree and drill down.

Thanks everyone for all your help. I really appreciate it.

Linda