Hi,
I have a difficulty finding the difference between the 2 timestamps.

The date, time and AM/PM information are getting passed from the launch page. I need to pull the data for date and time range mentioned in the launch page.
Ex - date1 = 2010/02/01
date2 = 2010/02/02
time1 = 10:00PM
time2 = 04:00AM
the report should run for these 2 days for the time range between 10:00PM to 04:00AM

This message has been edited. Last edited by: Kerry,

Hi,

If I understand correctly then you could convert each date to a datetime with something along the lines of:

DATE_R_TEMP1/DMYY = DATE_RAISED;
DATE_R_TEMP2/A8DMYY = DATE_R_TEMP1;
DATE_R_TEMP3/A10 = DATE_R_TEMP2;
DATE_R_TEMP_YR/A4 = EDIT(DATE_R_TEMP3, '$$$$9999');
DATE_R_TEMP_MT/A2 = EDIT(DATE_R_TEMP3, '$$99$$$$');
DATE_R_TEMP_DY/A2 = EDIT(DATE_R_TEMP3, '99$$$$$$');
DATE_R_TEMP4/A8 = DATE_R_TEMP_YR | DATE_R_TEMP_MT | DATE_R_TEMP_DY;
TM_R_TEMP1/A8 = TIME_RAISED;
TM_R_TEMP2/A6 = EDIT(TM_R_TEMP1, '99$99$99');
TM_R_TEMP3/A8 = EDIT(TM_R_TEMP2, '99:99:99');
DATE_TIME_R_TEMP/A20 = DATE_R_TEMP4 | ' ' | TM_R_TEMP3;
DATE_TIME_R1/HYYMDS = HINPUT(20, DATE_TIME_R_TEMP, 10, DATE_TIME_R1);

Do the same with the second datetime

Then calculate the difference:

DATEDIF (DATE_TIME_R1, DATE_TIME_R2, 'D');

There is probably a more efficient way to do this, but this does work for me!

Jinx.

Have a look at this thread: http://forums.informationbuild...971057331/m/90910134 and see if that helps (especially the last post). You may have to concatenate your beginning date and time and your ending date and time to a single field, then do the comparison.

- ABT

What is/are the format of your database date/time column/s?

quote:

I have a difficulty finding the difference between the 2 timestamps

quote:

the report should run for these 2 days for the time range between 10:00PM to 04:00AM

Shru, I don't think I understand your need correctly. Do you need to retrieve data in a date/time range or do you need to calculate the difference between 2 timestamps?

If the former one applies, you might find the DT function very useful.

You can quickly retrieve records in a date/time range by using something like:

-SET &DATE1=20100201;
-SET &DATE2=20100202;
-SET &TIME1='10:00:00';
-SET &TIME2='16:00:00';
-*
TABLE FILE your_table
PRINT your_field
WHERE you_datestamp_field FROM DT(&DATE1 &TIME1) TO DT(&DATE2 &TIME2)
END

Read some documentation about the DT function to determine which format you need to use when passing parameters to it.

There is a very good book available that gives you many many functions onluy onm dates
"(Almost) 1001 ways to work with dates in WebFocus"
ISBN: 978-0-9791711-1-2

I payed 25 USD...

hi njsden,

i tried using DT function. But by using DT it will give me the complete range from start date to end date
ex : 2009/12/01 23:00:00- 2009/14/10 06:00:00
DT will give the records from 23:00:00 of 12 day to 06:00:00 of 14th day.
but what i am looking at is the records those are entered only through the time frame 23:00:00 - 06:00:00.

I just want to mention that the time is entered in 12 hour format in the launch page but in the database it is military format

If I understand your requirement correctly, I think njsden's suggestion can still solve your challenge:

TABLE FILE your_table
PRINT your_field
WHERE your_datestamp_field FROM DT(&DATE1 &TIME1) TO DT(&DATE1 &TIME2)
   OR your_datestamp_field FROM DT(&DATE2 &TIME1) TO DT(&DATE2 &TIME2);
END

quote:

I just want to mention that the time is entered in 12 hour format in the launch page but in the database it is military format

Hi Shru, as far as I know DT() requires time in a 24-hour format. If your input is provided in a 12-hour format you'll have to convert it before passing it to DT() which is very easy to do; you may need to play a little with GETTOK to parse &TIME1 and obtain each time component.

Dan's example show you how to retrieve your records in a specific time frame. It has nothing to do with the use of the DT() function but in how you structure your selection criteria, which he clearly outlines there.

Regards,
- Neftali.