11 Replies Latest reply on Jul 4, 2014 9:07 PM by rp0428

    Convert String to date and add days to it.

    Murali_Srini

      Hi folks,

       

      I have two variables as shown and one of them is not in actual DATE format.

       

      FE_AC_END_DATE is 'DEC2011'

      FE_PR_END_DATE is 31-DEC-10

       

       

      show to_date(FE_AC_END_DATE, 'MONYYYY')

      01-DEC-10

       

      How to make sure that after conversion i get it as 31-DEC-10.

      It should always give me end of the month date and not the star of the month date.

       

      say if FE_AC_END_DATE is 'SEP2009', then after conversion i should get 30-SEP-2009.

       

      Is there away i can months to it?? What ever i do it has to be in a single line syntax. Any delivered function to use??

       

      Secondly when you use BETWEEN to compare the date range would it also inlcude start and end range ????

       

      For ex.,

       

      WHEN END_DATE BETWEEN to_date(DATE1, 'MONYYYY') AND to_date(DATE2, 'MONYYYY')

       

      What would happen if the END_DATE is equal to either DATE1 and DATE2? Would it still be treated as in BETWEEN ?

       

      Appreciate your inputs.

        • 1. Re: Convert String to date and add days to it.
          Jagdeep Sangwan

          Murali_Srini


          For your first question you might use

           

          with d as(
          select
            'DEC2011' FE_AC_END_DATE
          from
            dual
          )
          select
            last_day(to_date(FE_AC_END_DATE, 'MONYYYY'))
          from 
            d
          ;
          

           

          and for your second you might have to understand and play with date and time in oracle.

           

          Regards

          Jagdeep Sangwan

          1 person found this helpful
          • 2. Re: Convert String to date and add days to it.
            2683628

            HI

             

            Try this:

             

            select to_char(last_day(to_date('DEC2011', 'MONYYYY')),'DD-MON-YYYY') from dual;

             

             

             

            select to_char(

            add_months(last_day(to_date('DEC2011', 'MONYYYY')),3),

            'DD-MON-YYYY') from dual;

             

             

             

            For between it should be inclusive - but beware of the time portion of the date field, best to ensure that the first start one at 00:00:00 and the end one finishes at 23:59:59. Try this and verify

            • 3. Re: Convert String to date and add days to it.
              Murali_Srini

              Thanks Guys! I am still doubtful of the BETWEEN clause. Looks like I will have to truncate and not use the 'time' portion of the DTTM string for range comparison.

              • 4. Re: Convert String to date and add days to it.
                Murali_Srini

                Do we have similar function for FIRST DAY of the month ???

                • 5. Re: Convert String to date and add days to it.
                  Jagdeep Sangwan

                  you can check and use the trunc function to get these kind of data

                   

                  select
                    trunc(sysdate, 'mm')
                  from
                    dual
                  ;
                  
                  TRUNC(SYS
                  ---------
                  01-JUL-14
                  
                  

                   

                  Regards,

                  Jagdeep Sangwan

                  • 6. Re: Convert String to date and add days to it.
                    KarK

                    You can use TRUNC to get the first day of the month.

                     

                    SQL> SELECT TRUNC (SYSDATE, 'month') from dual;

                     

                    TRUNC(SYS
                    ---------
                    01-JUL-14

                    • 7. Re: Convert String to date and add days to it.
                      brunovroman

                      Hello Murali,

                       

                      adding some explanations about "BETWEEN": as dates CAN contain hour:min:sec that are not 00:00:00 it is "easy" to make a mistake with "BETWEEN".

                      BETWEEN a AND b means: in [a  b]  (both extremities included), but if we speak of DATEs, it is dangerous: in "natural language" we say "up to 04-JUL-2014" and we are tempted to write  BETWEEN xxx AND TO_DATE( '04/07/2014', 'DD/MM/YYYY' )

                      But doing this we consider only the second "00:00:00" of the day and anything >= 04-JUL-2014 00:00:01 is excluded. In fact we would like to have a "between_bis" that would mean [a  b[ (excluding b), then we would ask between_bis 01-JUL and 05-JUL

                       

                      So even if you think that your data does not contain HH:MI:SS > 00:00:00, it is wise to use code that would still be OK even with any HH:MI:SS

                      Two options:

                      -a- mydate BETWEEN a AND TO_DATE( '04/07/2014 23:59:59', 'DD/MM/YYYY HH24:MI:SS' )

                      -b- don't use BETWEEN but rather 2 conditons: mydate >= a AND mydate < 1 + TO_DATE( '04/07/2014', 'DD/MM/YYYY' )

                      I prefer the second way (that would still be OK if for example in the future Oracle DATE inherits from Oracle TIMESTAMP's "fractional seconds")

                       

                      Last remark: a constant date can be written as DATE 'YYYY-MM-DD', so the way I prefer for "mydate between July 1st and July 4th" is:

                        WHERE mydate >= DATE '2014-07-01' AND mydate < DATE '2014-07-05'

                       

                      Best regards,

                       

                      Briuno Vroman.

                      1 person found this helpful
                      • 8. Re: Convert String to date and add days to it.
                        Murali_Srini

                        Thanks Briuno for all the details. That is very helpful indeed.

                         

                        I still can go ahead and use mydate >= DATE '2014-07-01' AND mydate <= DATE '2014-07-05' because I want to make i include both the extremities for my range comparison. Since this is going to impact a lot calculation further down in my code, I just want to make sure i get this one correct.


                        Thanks,

                        Murali



                        • 9. Re: Convert String to date and add days to it.
                          Frank Kulash

                          Hi, Murali,

                          Murali_Srini wrote:

                           

                          Thanks Briuno for all the details. That is very helpful indeed.

                           

                          I still can go ahead and use mydate >= DATE '2014-07-01' AND mydate <= DATE '2014-07-05' because I want to make i include both the extremities for my range comparison. Since this is going to impact a lot calculation further down in my code, I just want to make sure i get this one correct.


                          Thanks,

                          Murali



                          What you posted will iinclude midnight on July 5, but not 00:00:01 or any later time on July 5.

                          If you want to include all of July 4, but none of July 5, then change <= to <, like this

                           

                               mydate >= DATE '2014-07-01'

                          AND  mydate <  DATE '2014-07-05'

                          • 10. Re: Convert String to date and add days to it.
                            brunovroman

                            Hello,

                             

                            "mydate <= DATE '2014-07-05'", really?  Be sure to understand what you ask in this case.

                            For example: do you really want:
                                mydate = Jul. 5th at 00:00:00 => accepted.
                                mydate = Jul. 5th at 10:30:01 => rejected.
                                mydate = Jul. 5th at 23:59:59 => rejected.
                                mydate = Jul. 5th at 00:00:01 => rejected.

                            It seems weird to accept only the first second of a day... Most probably you want all the day or nothing, hence:

                            "mydate < DATE '2014-07-05'" if I want "up to 04-JUL-2014 23:59:59.999, but NOT 05-JUL-2015"

                             

                            or "mydate < DATE '2014-07-06'" if I ant also the 5th. of July.


                            Remark: if you prefer, you can also use the "date arithmetic": if I want all the fourth of July but not the fifth, I can write:

                            "mydate < 1 + DATE '2014-07-04'"

                             

                            The important is to "visualize" a day as an interval (from 00:00:00 to 23:59:59.9999), then you can have a clear view of what are exactly your requirements, and then you can write the conditions to express this correctly.

                             

                            Best regards,

                             

                            Bruno.

                            • 11. Re: Convert String to date and add days to it.
                              rp0428
                              Secondly when you use BETWEEN to compare the date range would it also inlcude start and end range ????

                              For documentation questions refer to the documentation for  your version

                              http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm

                              And the value of

                              expr1 BETWEEN expr2 AND expr3  

                              is the value of the boolean expression:

                              expr2 <= expr1 AND expr1 <= expr3