#100CodeExamples – SYSDATE vs. CURRENT_DATE
Tags: 100codeexamples, sql, utplsql, oracle
Published: 2019-01-15 11:06:15

---
title: #100CodeExamples – SYSDATE vs. CURRENT_DATE
published: true
tags: #100CodeExamples,SQL,utPLSQL,Oracle
canonical_url: https://cleandatabase.wordpress.com/2018/12/18/100codeexamples-sysdate-vs-current_date/
---

I ran into a strange error yesterday: when I ran my test-suite from IDE I suddenly got a number of failed tests, but they were completely okay when I ran it from utPLSQL-cli.

I panicked a bit because that’s one of the worst possible situations you can imagine for a testing framework.  
After some investigation, I found the problem.  
Look at the following example-test suite which assures that the current date and time is set for “arrival” when a new entry is inserted into the “starport\_flights”-table:

```sql
/* Simple table which contains starship-flights */
create table starport_flights (
  id integer not null primary key,
  ship_id integer not null,
  arrival date default sysdate,
  departure date
);
 
create or replace package ut_starport as
  -- %suite(Starport functionality)
 
  -- %test(Ship gets Arrival date on insert)
  procedure ship_gets_default_arrival;
end;
 
create or replace package body ut_starport as
  procedure ship_gets_default_arrival
  as
    /* Expected arrival is the current date */
    l_expected_arrival date := current_date;
    l_actual_arrival date;
    begin
      /* Act */
      insert into starport_flights ( id, ship_id )
        values ( -1, -1 );
 
      /* Assert: Actual arrival should be within 5
         seconds more or less than the expected arrival */
      select arrival into l_actual_arrival
        from starport_flights where id = -1;
 
      ut.expect(l_actual_arrival)
        .to_be_between( /* Assert with a bit of inaccuracy */
          l_expected_arrival - interval '5' second,
          l_expected_arrival + interval '5' second
        );
    end;
end;
/
 
call ut.run('ut_starport');
```
```
Starport functionality
   Ship gets Arrival date on insert [,312 sec] (FAILED - 1)
 
Failures:
 
   1) ship_gets_default_arrival
       Actual: 2018-12-18T19:47:25 (date) was expected to be between: 2018-12-18T21:47:20   and 2018-12-18T21:47:30
       at "SITHDB.UT_STARPORT.SHIP_GETS_DEFAULT_ARRIVAL", line 16 ut.expect(l_actual_arrival)
 
Finished in ,312538 seconds
1 tests, 1 failed, 0 errored, 0 disabled, 0 warning(s)
```

Looks like the session in my IDE had a different timezone set than my database, while the fresh session in utPLSQL-cli had the same timezone as the database.

Depending on what you want to achieve, you should use SYSDATE or CURRENT\_DATE consistently.

The following little function shows how those two differ:

```sql
/* Simple procedure to output sysdate and current_date
   Difference is CURRENT_DATE - SYSDATE */
create or replace procedure output_dates
as
  l_sysdate date := sysdate;
  l_dbtimezone varchar2(16) := dbtimezone;
  l_curdate date := current_date;
  l_sessiontimezone varchar2(16) := sessiontimezone;
  begin
    dbms_output.PUT_LINE(
      'Sysdate (' || l_dbtimezone || '): '
      || to_char(l_sysdate, 'HH24:MI')
      || ', Current_Date (' || l_sessiontimezone || '): '
      || to_char(l_curdate, 'HH24:MI')
      || ', Difference (in hours): '
      || to_char((l_curdate-l_sysdate)*24));
  end;
/
 
alter session set time_zone = '-6:00';
call output_dates();
 
alter session set time_zone = '+2:00';
call output_dates();
```
```
Sysdate (+00:00): 19:46, Current_Date (-06:00): 13:46, Difference (in hours): -6
Sysdate (+00:00): 19:46, Current_Date (+02:00): 21:46, Difference (in hours): 2
```

You can run this on [LiveSQL](https://livesql.oracle.com/apex/livesql/s/hpdq7h7zfosuh80auflesyiyl).

The full example is available on [GitHub](https://github.com/pesse/sith-demo-db/blob/master/100CodeExamples/5_sysdate_vs_current_date.sql).