Results 1 to 4 of 4

Thread: Sysdate

  1. #1
    Join Date
    Aug 2003
    Posts
    6

    Unanswered: Sysdate

    Is there a way to give the SYSDATE a date.

    Here is what I am trying to do, I have a functions that uses SYSDATE, and for testing purposes, I need the SQL database to think that the SYSDATE is Friday instead of today, Monday. Any help would be greatly appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Sysdate

    You'd have to reset the system clock on the test server, I believe.

    Alternatively: wrap SYSDATE in a packaged function, then you could adjust the returned value any way you like like this:

    Code:
    create or replace package utils as
      function currdate return date;
      procedure set_offset( p_offset in number );
    end;
    /
    
    create or replace package body utils as
      g_offset number := 0;
    
      function currdate return date
      is
      begin
        return SYSDATE + g_offset;
      end;
    
      procedure set_offset( p_offset in number )
      is
      begin
        g_offset := p_offset;
      end;
    
    end;
    /
    For example:

    SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MIS';

    Session altered.

    SQL> select sysdate, utils.currdate
    2 from dual;

    SYSDATE CURRDATE
    -------------------- --------------------
    23-SEP-2003 14:18:32 23-SEP-2003 14:18:32

    SQL> exec utils.set_offset(-3)

    PL/SQL procedure successfully completed.

    SQL> /

    SYSDATE CURRDATE
    -------------------- --------------------
    23-SEP-2003 14:18:47 20-SEP-2003 14:18:47

    Of course, by doing this you are introducing a call to a PL/SQL function, which could impact performance if used heavily in SQL statements.

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    if you want 'select sysdate from dual' to return friday instead of monday, you 'll have to change dat and time of the database server and restart your database.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  4. #4
    Join Date
    Oct 2003
    Posts
    3

    Re: Sysdate

    HI,

    You could use "SYSDATE-4"
    instead of "SYSDATE"
    so you would avoid changing your original sysdate.. would that do it for u?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •