5 Replies Latest reply on Jan 21, 2019 8:31 AM by B.Delmée

    Decode and Between

    Shiva Ramachandran

      Hi

       

      In my sql query I want to have a column where in the if the date is between a particular range then it should the year.

       

      Eg below

       

      Decode (aia.gl_date between  '01-Feb-2012' AND '31-Mar-2012','2011-2012')

       

      When using the above it is giving me error.

       

      Can somebody guide me.

       

      Regards

       

      Shiva

        • 1. Re: Decode and Between
          cormaco

          Don't store date values as strings, here is a fine document how to work with dates correctly:

          PL/SQL 101 : DataTypes - DATE

           

          With regard to your question this gives what you want:

          with aia(gldate) as (select '10-Feb-2012' from dual union all select '10-Jan-2012' from dual)
          select 
              aia.gldate,
              case 
                  when 
                      to_date(aia.gldate,'DD-Mon-YYYY') between to_date('01-Feb-2012','DD-Mon-YYYY') and to_date('31-Mar-2012','DD-Mon-YYYY') 
                  then 
                      '2011-2012'
                  else
                      null
              end as result
          from aia
          
          
          GLDATE      RESULT   
          ----------- ---------
          10-Feb-2012 2011-2012
          10-Jan-2012          
          
          
          • 2. Re: Decode and Between
            Shiva Ramachandran

            Hi

            Thanks for your reply if to group on the above how should I do it.

             

            Regards

             

            Shiva

            • 3. Re: Decode and Between
              cormaco
              with aia(gldate) as (select '10-Feb-2012' from dual union all select '10-Jan-2012' from dual)
              select result,count(*) from 
              (
                  select 
                      aia.gldate,
                      case 
                          when 
                              to_date(aia.gldate,'DD-Mon-YYYY') between to_date('01-Feb-2012','DD-Mon-YYYY') and to_date('31-Mar-2012','DD-Mon-YYYY') 
                          then 
                              '2011-2012'
                      end as result
                  from aia
              )
              group by result
              
              
              RESULT      COUNT(*)
              --------- ----------
                                 1
              2011-2012          1
              
              
              1 person found this helpful
              • 4. Re: Decode and Between
                Shiva Ramachandran

                Thanks for your prompt reply

                 

                Regards

                 

                Shiva

                • 5. Re: Decode and Between
                  B.Delmée

                  Note this forum is for questions related to the SQLDeveloper GUI tool. For generic SQL/PLSQL questions, please post here in the future.
                  Here is another link to their fine posting guidelines on how to best explain your issue and help others help you.