4 Replies Latest reply: Nov 17, 2012 4:24 AM by jeneesh RSS

    Does SYSDATE change across the program or it is fixed for a program?

    Prasanth S
      Hi,

      I have a procedure and using sysdate in many places..say it takes 10 mins to complete..sysdate at first line and sysdate and last line will it be same or different?

      We have procedured that should be sheduled on 11:50 PM if it takes more than 10 mins and completing beyond 12 o clock..will it impact on sysdate used in procedures..?
        • 1. Re: Does SYSDATE change across the program or it is fixed for a program?
          Chanchal Wankhade
          hi,

          If you want to use the same date of sysdate better you declare a variable, insert a value to the varial from dual and use the variable wherever you need.

          You can use like.....
          declare
          v date;
          begin
          select to_char(sysdate,'dd-mm-yyyy') into v from dual;
          end;
          • 2. Re: Does SYSDATE change across the program or it is fixed for a program?
            jeneesh
            user574397 wrote:
            Hi,

            I have a procedure and using sysdate in many places..say it takes 10 mins to complete..sysdate at first line and sysdate and last line will it be same or different?

            We have procedured that should be sheduled on 11:50 PM if it takes more than 10 mins and completing beyond 12 o clock..will it impact on sysdate used in procedures..?
            It will be different and should be different

            Suppose you have a code like below
            begin
              --"line 1
              ........select * from ..... where date_column = trunc(sysdate); 
              ......
              .....
              --"line 100
              .......select * from ..... where date_column = trunc(sysdate); 
              .......
            end;
            If your code between line1 and line100 takes 10 minute to complete, thensysdate@ine100 will be sysdate@line1+10minutes

            Now, if you want to make them same, declare a variable, assign sysdate to it and use it wherever you want.
            declare
             ld_date1 date := trunc(sysdate);
            begin
              ......select * from ..... where date_column = ld_date1;/*trunc(sysdate); */
              ......
              .....
              .....select * from ..... where date_column = ld_date1;/*trunc(sysdate); */
              .....
            • 3. Re: Does SYSDATE change across the program or it is fixed for a program?
              dariyoosh
              user574397 wrote:
              Hi,

              I have a procedure and using sysdate in many places..say it takes 10 mins to complete..sysdate at first line and sysdate and last line will it be same or different?

              We have procedured that should be sheduled on 11:50 PM if it takes more than 10 mins and completing beyond 12 o clock..will it impact on sysdate used in procedures..?
              Hello there,


              Here is how oracle defines the SYSDATE function:
              http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions191.htm#SQLRF06124
              >
              +SYSDATE returns the current date and time set for the operating system on which the database server resides. The data type of the returned value is DATE, and the format returned depends on the value of the NLS_DATE_FORMAT initialization parameter. The function requires no arguments. In distributed SQL statements, this function returns the date and time set for the operating system of your local database. You cannot use this function in the condition of a CHECK constraint.+
              >

              Here is an example:
              SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM DUAL;
              
              TO_CHAR(SYSDATE,'DD-
              --------------------
              17-NOV-2012 08:38:12
              Now if you run the same command a few seconds later, here is what you get
              SQL> SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM DUAL;
              
              TO_CHAR(SYSDATE,'DD-
              --------------------
              17-NOV-2012 08:38:22
              
              SQL> 
              As you can see the returned value evolves as a function of time. As a result given the impact of the date value on your program you might want to save its value at the beginning of your procedure.

              Regards,
              Dariyoosh
              • 4. Re: Does SYSDATE change across the program or it is fixed for a program?
                jeneesh
                Chanchal Wankhade wrote:
                select to_char(sysdate,'dd-mm-yyyy') into v from dual;
                Dont do this in PL/SQL code..

                Why do you wnat to use SQL for something which is easily doable in PL/SQL?

                And why are you using TO_CHAR to populate date variable?

                It is simply
                v := trunc(sysdate);