Embedded SQL number of days between dates
C/EXEC SQL C+ INSERT INTO SERIOVR60 C+ SELECT HTSERN, HTPART, HTQTY, HTSTKL, C+ HTSTS, HTADAT, C+ substr(HTPART, 1, 11) AS HTMODEL, C+ HTADAT - :CURDATE AS HTDAYS C+ FROM SERI C+ WHERE HTADAT < :TESTDATE and C+ HTADAT <> '0001-01-01' and C+ HTSTS = 'A' C+ ORDER BY HTPART C/END-EXEC
This returns;
Date = 2010-05-12
Days = 530
Is there a %diff in SQL, or subdur-like operation in SQL?
Thanks,
Nick
Software/Hardware used:
as/400, sqlrpgle
This returns;
Date = 2010-05-12
Days = 530
Is there a %diff in SQL, or subdur-like operation in SQL?
Thanks,
Nick
Software/Hardware used:
as/400, sqlrpgle
ASKED:
November 11, 2010 5:07 PM
UPDATED:
November 13, 2010 3:45 AM
Answer Wiki:
Carlos,
Thanks. This is what I ended up doing and it gives me correct data. I think why I was getting a negative number is that the dates were flip/flopped. A college suggested ABS() around the statement but this is what I did. I hope it posts formated so it can be read.
C/EXEC SQL SET OPTION commit=*none, closqlcsr=*endmod
C/END-EXEC
*
C/EXEC SQL
C+ INSERT INTO SERIOVR60
C+ SELECT HTSERN, HTPART, HTQTY, HTSTKL,
C+ HTSTS, HTADAT,
C+ substr(HTPART, 1, 11) AS HTMODEL,
C+ DAYS(CURRENT DATE) - DAYS(HTADAT) AS HTDAYS
C+ FROM SERI
C+ WHERE HTADAT < :TESTDATE and
C+ HTADAT <> :DATEDFT and
C+ HTSTS = 'A'
C+ ORDER BY HTPART
C/END-EXEC
To see all answers submitted to the Answer Wiki: View Answer History.
It seems that substracting two dates will produce a result in the format of YYMMDD, son in this case 530 would mean 5 months, 30 days.
I just googled it, and maybe the DAYS function is what you are looking for.
But I don’t really know if this function is available on the AS400. Let’s wait for someone to confirm, or correct my suggestion.
DAYS( ) should work. I use it frequently in SQL. The trick is to make sure the variable supplied is recognized as a date by SQL. When embedded in RPG, using the RPG date functions are much more powerful.