This discussion is archived
4 Replies Latest reply: Nov 17, 2012 2:24 AM by jeneesh RSS

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

Prasanth S Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points