DateDiff

[LeonelSanchezJr]

I need to find out the DIFFERENCE in HOURS between two DATETIME fields that exist in ORACLE.



Thanks,

Leo ;-)

 

[craigiep]

This has been documented elsewhere, which is where I got this. It's set up as a DEFINE FUNCTION.

-***********************************************************
-* Function to calculate the time difference between 2
-* oracle timestamps
-*
-* Argument Name Argument Format
-* ============= ===============
-* End Date HYYMDS (YYMMDD HH:MM:SS)
-*
-* Start Date HYYMDS (YYMMDD HH:MM:SS)
-*
-* Day Indicator A1 Y - display
-*days / N no day display
-*
-* Seconds Indicator A1 Y - display
-* seconds/N no seconds
-*
-* HDIFE (output) A25 [n DAYS] hh:mm[:ss]
-*
-* The function may be in an EDASPROF or any FEX.
-*
-*DAY IS Y IF YOU WNAT TO SEE DAY -*
-* SECS IS Y IF THE CALCULATION AND DISPLAY SHOULD CONTAIN
-* SECONDS
-*********************************************************
DEFINE FUNCTION HDIFE/A25 (ENDT/HYYMDS, STDT/HYYMDS, DAY/A1, SECS/A1)

PARM1/A7 = IF SECS EQ 'Y' THEN 'SECONDS' ELSE 'MINUTE';

-* determine difference in minutes or seconds based on argument 4

HDIFF/D12.2 = HDIFF (ENDT,STDT,PARM1,'D12.2');

-* Calculate Days if argument 3 set to Y

DAYS/I5= IF DAY EQ 'Y' AND SECS EQ 'Y' THEN

HDIFF / (24 * 60 * 60) ELSE IF DAY EQ 'Y'

THEN HDIFF / (24 * 60) ELSE 0;

DAYEQ/I9 = IF SECS EQ 'Y' THEN DAYS * 3600 * 24 ELSE DAYS * 60 * 24;

DAYALPHA/A12 = IF DAY EQ 'Y' THEN

(FTOA(DAYS,'(D5)','A6')) || ' DAYS ' ELSE ' ';

-* Convert minutes or seconds remaining to Integer # hours

HRS/I5 = IF DAY EQ 'Y' AND SECS EQ 'Y' THEN

(HDIFF - (DAYS * 24 * 3600)) / 3600 ELSE

IF SECS EQ 'Y' THEN HDIFF / 3600 ELSE

IF DAY EQ 'Y' THEN (HDIFF - (DAYS * 24 * 60))/60 ELSE

HDIFF / 60 ;

HRALPHA/A6 = LJUST(5,(FTOA(HRS,'(D5c)','A5')),'A5');

HREQ/I9 = IF SECS EQ 'Y' THEN HRS * 3600 ELSE HRS * 60;

-* Convert minutes or seconds remaining to minutes

MINS/I2 = IF SECS EQ 'Y' THEN (HDIFF - DAYEQ - HREQ) / 60 ELSE

HDIFF - DAYEQ - HREQ ;

MINEQ/I9 = IF SECS EQ 'Y' THEN MINS * 60 ELSE MINS;

MINALPHA/A2 = EDIT(MINS);

-* remainder is seconds (if argument 4 is Y)

SECONDS/I2S= IF SECS NE 'Y' THEN 0 ELSE

HDIFF - DAYEQ - HREQ - MINEQ;

SECALPHA/A3 = IF SECS EQ 'Y' THEN ':' | EDIT(SECONDS) ELSE ' ';

-* establish the display

HDIFE/A25 = DAYALPHA || (' ' | HRALPHA) || ':' || MINALPHA || SECALPHA;
END