SQL Date Handling
Last Post 28 Mar 2013 04:12 PM by Rocky Marquiss. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gregwga50
Advanced Member
Advanced Member
Posts:472

--
28 Mar 2013 08:13 AM
I have a file that has 2 fields that contain dates in the format yyyymmdd. Using SQL, I want to find out how many days between them. Note: These are not DATE type fields, they are Signed (8,0)
novacancy
Basic Member
Basic Member
Posts:125
Avatar

--
28 Mar 2013 08:39 AM

maybe something like this (not tested)

 

create table qtemp/mytable        
(date1 dec(8,0),date2 dec(8,0))   

insert into qtemp/mytable  
values(20130329,20130429)  

select days(date(digits(date2) concat '000000')) -
       days(date(digits(date1) concat '000000'))   
from qtemp/mytable                                
 

Enduring the V5R3 blues
Rocky Marquiss
Senior Member
Senior Member
Posts:2906

--
28 Mar 2013 03:48 PM
SELECT DATE(to_date(char(datefld1),'YYYYMMDD')) -       
DATE(to_date(char(datefld2),'YYYYMMDD')) ....

 This will return the difference in format YYMMDD

20130313 - 20130101 gives 27 - 27 days

20130328 - 20130101 gives 227 - 2 months, 27 days

20131231 - 20130101 gives 1130 - 11 Months, 30 days

20181231 - 20130101 gives 51130 - 5 years, 11 months, 30 days.

Rocky Marquiss
Senior Member
Senior Member
Posts:2906

--
28 Mar 2013 04:08 PM
Edited - delete if I knew how....
Rocky Marquiss
Senior Member
Senior Member
Posts:2906

--
28 Mar 2013 04:12 PM
SELECT DATEFLD1, DATEFLD2,days(to_date(char(datefld1),'YYYYMMDD')) -
days(to_date(char(datefld2),'YYYYMMDD'))..

Gives the following results

 

DATEFLD1 - DATEFLD2 = Numeric Expression
20130328   20130301                 27  
20130328   20130101                 86  
20131231   20130101                364  
20141231   20130101                729  
20181231   20130101              2,190  
You are not authorized to post a reply.

Acceptable Use Policy