Embedded SQL number of days between dates

1380 pts.
Tags:
AS/400
Embedded SQL
SQL
SQLRPGLE
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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.
    DAYS(:CURDATE) - DAYS(HTADAT)
    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.
    69,225 pointsBadges:
    report
  • graybeard52
    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.
    3,115 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following