Calculate a Duration as # of Days

[littlepd]

I had a problem this afternoon that was giving me fits. The problem was how to take two dates and determine the number of days between them within a SELECT statement. It was harder than I thought it would be!

First off, the fields in the table are not date-type fields, but 8-byte character fields. The values are formatted like *ISO fields (without the dashes). For example, last Friday would be "20070112". So the first thing I had to do was convert that to an actual date. I did that like this:

DATE(SUBSTR(OrderDate, 1, 4) || '-' ||
     SUBSTR(OrderDate, 5, 2) || '-' ||
     SUBSTR(OrderDate, 7, 2))

The DATE() function returns a date from a value. The argument must be an expression that returns a value of one of the following built-in data types: a date, a timestamp, a character string, a graphic string, or any numeric data type.

So now to figure out the number of days that have elapsed since the Order Date. To get today's date as a date-type field, I used the CURDATE() function. Simple enough. Now just subtract one from the other to get the duration.

CurDate() - DATE(SUBSTR(OrderDate, 1, 4) || '-' ||
                 SUBSTR(OrderDate, 5, 2) || '-' ||
                 SUBSTR(OrderDate, 7, 2)) as Duration

All done, right? WRONG!

As it turns out, the result from this calculation is a number that represent the Months and Days since Order Date! So for December 15, 2006, as the order date, the result of the above calculation was 104. Not 104 days ago, but 1 month and 4 days ago! Useless!

What to do? What to do? I know! What if I could convert the date fields to integers that represent the number of days since some date a long, long time ago? Then, when I subtracted them, my result would be a number of days. That's where the DAYS() function comes in!

The DAYS() function (not to be confused with the DAY() function) returns an integer representation of a day that is the number of days between January 1, 0001, and the date passed in as the argument. Apply that to both dates, then subtract:

DAYS(CurDate()) - DAYS(DATE(SUBSTR(OrderDate, 1, 4) || '-' ||
                            SUBSTR(OrderDate, 5, 2) || '-' ||
                            SUBSTR(OrderDate, 7, 2))) as Duration

Again using 12/15/2006 for Order Date, this calculation returns 35, the answer I was looking for!

I hope this helps someone else who may run across this problem one day.

Thanks,
Michael

[jamief]

Re: Calculate a Duration as # of Days

Nice I can really use this........Thanks Have a great weekend!

[gcraill]

Re: Calculate a Duration as # of Days

Great example LP - well documented and presented, even I could follow it !!!
GC

[pramendra]

Re: Calculate a Duration as # of Days

it is really gud and some extent what i was looking for...

well done!!!!


Regards

[pramendra]

Re: Calculate a Duration as # of Days

i think it could be done with RPGLE .

Just sharing for knowledge purpose...

here test1 is pf which contain empdoj as character 20080729 which moved into @doj (NUMERIC).

diffdate will give the no of days between two years.

@CDOJ will have current date.

d @DOJ            s              8  0                                  
d @CDOJ           s               d                                    
d @CDATE          s               d   Datfmt(*ISO)                     
d @diffdat        s              8  0                                  
D W@DMY           S              6  0 Inz         
                     
C/EXEC SQL                                                             
C+ SELECT  EMPDOJ  INTO :@doj  FROM TEST1                              
C/END-EXEC         
                                                    
C                   EVAL      @CDOJ = %DATE(@DOJ : *ISO)  
             
C***if you want another format then....  other 
C* than  *iso then convert this way...   
                       
                                                                      
C     *DMY          Move      @CDOJ         W@DMY      
                
C                       EVAL      @cDATE  =%DATE()  

C                  EVAL      @diffdat = %DIFF(@cDATE :@CDOJ:*DAYS)

C*THIS WILL GIVE YOU NO OF DAYS. FOR GETTING MONTH VALUE PUT *MONTH INSTEAD *DAYS
                          
C     @diffdat       DSPLY                                     
C                   EVAL      *INLR=*on

Hope it will be very easy to understand...


Regards

[littlepd]

Re: Calculate a Duration as # of Days

"How to include source code in your posts."

[pramendra]

Re: Calculate a Duration as # of Days

Thanks fro ur link....

another way to deal with this problem..

C/EXEC SQL                                        
C+ SELECT CAST(EMPDOJ AS DATE) INTO : @CDOJ       
C+ FROM TEST1                                     
C/END-EXEC                                        
C/EXEC SQL                                        
C+ SET :@diffdat1=Days(CURDATE()) - DAYS(:@CDOJ)  
C/END-EXEC

where @cdoj is stand alone date variable
Test1 is physical file contain date as character(EMPDOJ)
CURDATE will give you the current date.

Regards

[B.Hauser]

Re: Calculate a Duration as # of Days

You statements will work if EMPDOJ has a character representation of a date in one of the following formats:
YYYY-DD-MM, MM/DD/YYYY, DD.MM.YYYY

But why using 2 SQL-Statements if a single one will be enough?

Exec SQL Select Days(Current_Date) - Days(Date(EMPDOJ)) 
          into :DiffDat1
          From Test1
          Fetch First Row Only; 

Birgitta

[pramendra]

Re: Calculate a Duration as # of Days

you are right Birgitta

thanks for making me correct 1nce again .As i always learnt from you ...


Welcome