2 Replies Latest reply on Mar 21, 2018 12:51 PM by lechkung

    next_day

    lechkung

      Hi,

      I got a problem if someone can help out.  I have a table so.patch_windows that I need to interpret a date from a column:

       

       

       

      select * from so.patch_windows

       

       

      IDPatchwindow
      11st Saturday at 9:00am
      22nd Friday at 9:00am
      32nd Thursday at 9:00am

       

       

      When I run the following code, it an "ORA-00932: inconsistent datatypes: expected DATE got CHAR" error.

       

       

      select id,

       

      case substr(patchwindow,1,1)

       

      when '1' then  next_day(trunc(sysdate,'mm')-1,  substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)  )

      --when '2' then substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)

      -- when '3' then substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)

      -- when '4' then substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)

      else patchwindow

       

      end 

      from  so.patch_windows;

       

       

       

      No matter what I do, I can't see to get past the error.  Can somebody help me out?

        • 1. Re: next_day
          L. Fernigrini

          The problem is in the ELSE clause:

           

          else patchwindow   

           

          "patchwindow" is a varchar, the results of the other when/then expressions are dates.

           

          This works perfectly:

           

          WITH patch_windows AS (SELECT 1 AS ID, '1st Saturday at 9:00am' AS PatchWindow FROM DUAL

                         UNION ALL

                         SELECT 2 AS ID, '2nd Friday at 9:00am' AS PatchWindow FROM DUAL

                         UNION ALL

                         SELECT 3 AS ID, '2nd Thursday at 9:00am' AS PatchWindow FROM DUAL

          )

          select id,

          case substr(patchwindow,1,1)

            when '1' then  next_day(trunc(sysdate,'mm')-1,  substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)  )

            --when '2' then substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)

            -- when '3' then substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)

            -- when '4' then substr(patchwindow,5,instr(patchwindow,' ',1,2)-5)

            ELSE to_Date('2018-03-21', 'YYYY-MM-DD')  -- RETURN A "DATE"

          END AS NextWindow

          from  patch_windows;

          1 person found this helpful
          • 2. Re: next_day
            lechkung

            DOH!   Can't believe that got by me!

            Lisandro,  Thank you so much!!!!