3 Replies Latest reply on Jan 19, 2016 8:23 PM by BrunoVroman

    time validation

    d59510c0-63a6-43cb-ba34-2d6a5acfd196

      I want to validate time in AM/PM format.If from_date and to_date are same then I need to check from_time & to_time based on AM/PM.

       

      EX:from_date:1/16/2016

      from_time:10 am

      To_date:1/16/2016

      to_time:9 am

      it should display error because when I enter 10am value on from_time,to_time field can't be 9am on the same day.it should display error.I want validation for this problem.

        • 1. Re: time validation
          Timo Hahn

          User, can you give us some information about the product you are using?

          It's not clear if you want to do this in java, pl/sql, C, C++, c# ....

           

          Once you have provided the info we can move the question to the right form. Or you look for the right forum yourself and post the question there again. In this case you should add the version of the product you are using. Help for this: Re: Introduce yourself!

           

          Timo

          • 2. Re: time validation
            Marwim

            Hello,

             

            you can concatenate the values and compute the difference

            TO_DATE('01/16/2016'||'10AM','mm/dd/yyyyhham')-TO_DATE('01/16/2016'||'09AM','mm/dd/yyyyhham')

            If this is negative, then you can throw an error.

             

            Regards

            Marcus

            • 3. Re: time validation
              BrunoVroman

              Hello,

               

              Oracle database has a datatype "DATE" that stores dates with a detail up to the second;

               

              And Oracle can "translate" strings of chars representing a date (even with the format using AM/PM) into its internal DATE datatype.

               

              There is a nice feature with Oracle DATEs: "date arithmetic": if "d2" and "d1" are two DATEs,

              - if we do d2 - d1 the result is the number of days between the two dates (and the result can have a decimal part as the number of days can be for example 0.000011574... for a difference of one second)

              - or if we add (or subtract) a number X to a DATE, the result is another DATE with the number X of days added (subtracted) ; example: SYSDATE - 3 / 24 is "three hours ago";  d1 + 185 / 1440 is "3 hours and 5 minutes after d1" ... (as there are 24 * 60 = 1440 minutes in 1 day, so 185 / 1440 day = 180 minutes + 5 minutes = 3H05')

               

              Knowing those points you should be able to use Oracle to "do the maths for you" ;-)

               

              And for your case you just have to know if a date is anterior to another; if you have stored your 2 dates in D1 and D2, it is as simple as

                  IF d1 < d2 THEN ... END IF;

               

              Best regards,

               

              Bruno Vroman.