How to compare numeric date field with other date fields in SQL?

220 pts.
Tags:
SQL
SQLRPGLE
Delete from LIBXXX/testB where orddtb<=080917 . The above code compare the date as numeric comparison. But I want date comparison. orddtb is declared as (6P 0). Please help me to compare numeric field date with real date. So that I can process the records. That Date field should be dynamic which depends upon the database I've read.(It may be like MMDDYY or DDMMYY or YYMMDD). I am using the query inside SQLRPGLE program.

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 3  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.
  • ToddN2000
    Post your current code. How do you tell what the date format coming in is? In my opinion the best way for doing date comparison with a 6 digit year is put them in a YYMMDD format if it's a straight compare. If you are looking for compare that are for a periods of time then the standard ISO date formats work best with SQL using the add days function.
    87,535 pointsBadges:
    report
  • rajeshkmr571
    Select * from LIB/TBL1
    O/P
    ORDER NO............  ITEM NO.............  ORDER DATE.(MDY)....  CUSTOMER NO.
                    1                     1             52,617                    
                    2                     2             52,717                    
                    3                     3             52,817                    
                    4                     4             52,917                    
                    5                     5             53,017                    

    select * from m1042106/trna where orddta >= '111016'
    ORDER NO............  ITEM NO.............  ORDER DATE. CUSTOMER NO..
    ********  End of data  ********                                                

    Where ORDDTA is numeric date field (6P 0).
    The above code is like number conversion not date conversion. I will read all the database file in a different libraray. So date format is not a static format it is like dynamic. I am using date to do purge operation.
    220 pointsBadges:
    report
  • ToddN2000
    @rajeshkmr571: the problem is the dates are in MMDDYY order. Your condition just looks at it as a number. None of those listed would be >+ 111,016.  in this example our fate is OHENTD stored in MMDDYY format. To do a > or < test we flip the date using this.

     int(substring(ohentd,5,2)*10000)+ int(substring(ohentd,1,4)))
    87,535 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.

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

Following

Share this item with your network: