ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Date "Math" on 8,0 INT Field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Date "Math" on 8,0 INT Field

    I want to convert an MS SQL Query to an IBM Query. I currently have MS SQL Code like:
    Code:
    CASE WHEN DATEDIFF(day, LM.LastRcdDateforLot, GETDATE()) >= 361 THEN '6'
    But my IBMi file has the equivalent date stored in an 8,0 field as 20200805

    I think I need to CAST the IBM column to DATE, then I can do a DIFF, but getting stuck on the syntax, or if it's even allowed to cast 8,0 as a date.

    Thoughts ?

  • #2
    You need to convert the numeric date into a real date.
    The easiest way for a numeric field defined as Dec(8, 0) with a numic date in the format YYYYMMDD is:
    Code:
    Date(Digits(YourNumDate) concat '000000')
    If you want to calculate the difference between 2 dates in days, you should use the DAYS function:
    Code:
    Days(FirstDate) - Days(SecondDate)
    Birgitta

    Comment


    • #3
      I believe you'll be able to do that with the following:

      PHP Code:
      days(date(LM.LastRcdDateforLot)) - days(current_date
      I regularly deal with 8:0s as dates and SQL always plays nice with them. Little more information and examples here https://developer.ibm.com/technologi...tes-and-times/

      Comment


      • #4
        I came up with this, which seems to work. Thanks for the other suggestions.

        Code:
        select days(current_date) - DAYS(DATE(TIMESTAMP_FORMAT(CHAR(LRDT),'YYYYMMDD')) )
        Last edited by MFisher; August 5, 2020, 09:12 AM.

        Comment

        Working...
        X