I have a question in regards to the datedif function. I need to calculate the datediff between the two returned date rows. in my print statement i am displaying the complete_access_form as 'time' this will display two rows. I need to calculate the datetime difference between these two dates. All help is appreciated. thank you a head of time.

-CODE--
SQL
select CASENUM, Complete_Access_Form,
datepart(dd,Complete_Access_Form) as 'day', datepart(yyyy,Complete_Access_Form) as 'year',datepart(mm,Complete_Access_Form) as 'month',
'Start Time' as 'Description'
from NOR_AUDIT_TRAIL
where TYPE_ID = 1
union all
select CASENUM, Complete_Access_Form,
datepart(dd,Complete_Access_Form) as 'day', datepart(yyyy,Complete_Access_Form) as 'year',datepart(mm,Complete_Access_Form) as 'month',
'Submit Time' as 'Description'
from NOR_AUDIT_TRAIL
where TYPE_ID = 2
;
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HOLD01 FORMAT ALPHA
END
TABLE FILE HOLD01
PRINT
Complete_Access_Form/HMDYYSA AS 'Time'
Description
BY
CASENUM AS 'Case Number'
WHERE ( CASENUM EQ &CaseNumber.(OR(FIND CASENUM IN NOR_AUDIT_TRAIL)).CaseNumber. ) AND ( year GE &Year.Year. AND month GE &Month.Month. OR (year GT &Year))
AND (year LE &Year2.Year. AND month LE &Month2.Month.);

DATEDIF is used for SmartDate fields (usage format MDY, YMD or DMY etc.).

HDIFF is the function used for DateTime fields (uasge format HMDY..., HYMD..., etc.)

HDISS syntax examples:
DIFF1/D6 = HDIFF(DT_HYYMD,DT_HMDYY,'DAY','D6') ;
DIFF2/D6 = HDIFF(DT_HYYMD,DT_HMDYY,'MONTH','D6') ;
DIFF3/D6 = HDIFF(DT_HYYMD,DT_HMDYY,'WEEK','D6') ;
DIFF4/D6 = HDIFF(DT_HYYMD,DT_HMDYY,'QUARTER','D6') ;
DIFF5/D6 = HDIFF(DT_HYYMD,DT_HMDYY,'HOUR','D6') ;
DIFF6/D6 = HDIFF(DT_HYYMD,DT_HMDYY,'MINUTE','D6') ;
DIFF7/D8 = HDIFF(DT_HYYMD,DT_HMDYY,'SECOND','D8') ;
DIFF8/D12= HDIFF(DT_HYYMD,DT_HMDYY,'MILLISECOND','D12') ;

HDIFF always requires a double precision format for the return value though whole numbers are returned.

Above examples are derived from:
"(Almost) 1001 Ways to Work with Dates in WebFOCUS"
which is available from www.aviter.com

Hope this helps,
John Price

sorry but i still do not understand how to get the differences in two dates that are returned returned from the query.
example
row 1 -- 01/01/2005
row 2 -- 01/05/2005
days difference -- 4 days

I hope that makes sence.

If the dates are returned as SmartDates the syntax would be:

DIFF2/I5 = DATEDIF(SMARTDATE, LAST SMARTDATE, 'D') ;


The LAST function allows you to access the previous row.

If you don't know what format the dates are, try taking the result to a hold file. If the USAGE format in the hold file is MDYY you have a SmartDate. If it's I8MDYY or A8MDYY you have a legacy date. If you have a legacy date convert it to a SmartDate before the above calculation. The syntax would be something like:

SmartDate/MDYY = RETURNDATE ;

Both of the above may be included in a COMPUTE statement.

Above examples are derived from:
"(Almost) 1001 Ways to Work with Dates in WebFOCUS"
which is available from www.aviter.com

Hope this helps,
John Price

thank you i will try this.

I tried to put the statement in a compute shown below and i get a syntax error: What is wrong with this? . I would like for the DATEDIF to show up on its own row if that is possiple, i am still learning web focus? thanks for your help john.

TABLE FILE HOLD01
PRINT
Complete_Access_Form/HMDYYSA AS 'Time'
Description
COMPUTE DIFF2/I5 = DATEDIF(Complete_Access_Form/HMDYYSA, LAST Complete_Access_Form/HMDYYSA, 'D') ;

BY
CASENUM AS 'Case Number'
WHERE ( CASENUM EQ &CaseNumber.(OR(FIND CASENUM IN NOR_AUDIT_TRAIL)).CaseNumber. ) AND ( year GE &Year.Year. AND month GE &Month.Month. OR (year GT &Year)) AND (year LE &Year2.Year. AND month LE &Month2.Month.);

It would be handy to know what syntax error message you received.

The line

Complete_Access_Form/HMDYYSA AS 'Time'

only reformats the column for the report output, not for any COMPUTE statements you may have.

I suspect the syntax error you received is because of this line - you cannot convert a date/time field from one format to another that easily.

First of all, please tell us what the format of Complete_Access_Form is, then we can tell you how to convert it and use it in calculations.

To determine what the format of the field is, temporarily put the following two lines before

TABLE FILE HOLD01

?FF HOLD01
-EXIT

This will give you the format of all the columns in HOLD01.

You were correct about the HMDYYSA part. but now i get some data that is not correct. when i use this line:
COMPUTE DIFF2/I5 = DATEDIF(Complete_Access_Form, LAST Complete_Access_Form, 'MINUTE');
i get
06/15/2005 2:47:12PM Start Time 0
06/15/2005 2:50:16PM Submit Time 347

when it should only be three minutes ?

wierd but now my data looks like this:
Case Time Description DIFF2
1 06/15/2005 11:47:12PM Start 7
06/15/2005 12:50:16PM Submit 42

2 06/15/2005 2:50:16PM Start 297
06/16/2005 3:50:16PM Submit -350

what i want to do is say: what is the difference in time between start and submit for each case number ?
should i even be using the DATEDIF command?
now i am all confused?
thanks

OK ITS ME AGAIN,
i found the last command in the help doc.
--***************************************
The LAST function retrieves the preceding value for a field.

The effect of LAST depends on whether it appears in a DEFINE or COMPUTE command:

In a DEFINE command, the LAST value applies to the previous record retrieved from the data source before sorting takes place.

In a COMPUTE command, the LAST value applies to the record in the previous line of the internal matrix.

Do not use LAST with the -SET command in Dialogue Manager
--***********************************
it says to use the define with the last command in order for it to retrieve before the sorting takes place. i tried to add the define command an it the report does not run

As per one of John's previous posts....

DATEDIF is used for SmartDate fields (usage format MDY, YMD or DMY etc.).

HDIFF is the function used for DateTime fields (uasge format HMDY..., HYMD..., etc.)


What is the original format of the fileds you are trying to get the difference between?????


If it is date/time use HDIFF not DATEDIF.

More than one person has asked you what the format of the date/time field is, but there has been no response to that question. I've suggested how you can determine what the format is by a command against the HOLD file.

Please tell us what the format is and maybe we can better help you.

As Piipster's posting pointed out, if the Compute_Access_Form field is a date time field you need to use the HDIFF function. Any format that uses the letter H is a date time format.

I noticed in your original posting that you are taking the results to a hold file (HOLD01). You should be able to look at the HOLD01 master to see the USAGE format.

Note that when using HDIFF the order of the input dates should be changed from the order in DATEDIF.
Also note that HDIFF always requires a double precision format for the return value.

Example:
DIFF1/D6 = HDIFF(LAST Complete_Access_Form,Complete_Access_Form,'MINUTE','D6') ;

If this does not resolve your problem, please post the related portions of the HOLD01 master file.

sorry it took me so long to get back to you guys thank you so much for your help. I will not be finishing this project till next week. As soon as i get web focus back up and running i will answer your questions. I will also try and use HDIFF

thank you so much.

mike

Hi Mike,

Just following-up on this issue with you. Do you have any updates on your progress?

Cheers,

Kerry

That worked! To a point; It did the difference between the the prior date which is ok. But i need only the prior date that matches the case number. I hope that makes sence.

When CASE NUMER is in the report, something like this should work:

DIFF1/D6 = IF CASENUM NE LAST CASENUM THEN your_preference_here ELSE
HDIFF(LAST Complete_Access_Form,Complete_Access_Form,'MINUTE','D6') ;