create a date/time field from two fields date/time & time

[dfor]

Using Crystal 8.5, I am trying to query events logged between two dates (datesql) and between two times (timesql); e.g. June 1 @ 08:00 to June 2 @ 07:59.

Datesql is a date/time field that does not capture any associated time value.
Timesql is a time value like: 08:30

I thought if I converted Datesql to a date vaule, it could be conbined with Datesql to give me a real date/time vaule to query. But I don't know how to do this.

Any help would be appreciated.

 

[dfor]

I mistated the data type for the Timesql field. It is a string. Thx

 

[rosemaryl]

Dear dfor,

If the date field is a datetimefield and the time field is a string and it is always formatted the same 2 digit hour, 2 digit min, then I think the following would work for you.

stringvar x := '08.30'; //replace with your string time fld
numbervar hr := tonumber(x[1 to 2]);
numbervar mn := tonumber(x[4 to 5]);

{Table.DateTimeField} + ctime(hr,mn,00)

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[dfor]

I guess I need more help than I thought. I added my table (see below) and when I check the formual, it asks for a number in front of ctime.

stringvar x := '08.30'; //replace with your string time fld
numbervar hr := tonumber(x[1 to 2]);
numbervar mn := tonumber(x[4 to 5]);

{PDDL.DATESQL} + ctime(hr,mn,00)

Thanks
Dori

 

[rosemaryl]

Dear Dfor,

Is the above an exact copy of your formula? I tested this and it does work. However, I note that you didn't replace the value in stringvar x with your string where indicated.

Please provide an exact copy of your formula.
Please double check all field types.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762