Home » SQL & PL/SQL » SQL & PL/SQL » Difference between sysdate and current_date (Oracle 10g EE - 10.2.0.3.0)
Difference between sysdate and current_date [message #451566] Thu, 15 April 2010 08:24 Go to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
What is the difference between the values of sysdate and current_date ?
On querying the database I got the results as
sysdate = the date and time for the database server location
and current_date = my local system date

If however, i change my local system date - it still shows the correct date.
Myunderstanding was that current_date uses the dbtimezone.

But..
SQL> select sysdate,current_date from dual;

SYSDATE              CURRENT_DATE
-------------------- --------------------
15-APR-2010 06:06:14 15-APR-2010 18:36:15

SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SQL> 
Re: Difference between sysdate and current_date [message #451567 is a reply to message #451566] Thu, 15 April 2010 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Myunderstanding was that current_date uses the dbtimezone

Wrong!

Maybe reading the documentation would help?
SYSDATE
CURRENT_DATE

Quote:
CURRENT_DATE returns the current date in the session time zone


Regards
Michel

[Updated on: Thu, 15 April 2010 08:32]

Report message to a moderator

Re: Difference between sysdate and current_date [message #451569 is a reply to message #451567] Thu, 15 April 2010 08:39 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
-------------------------------------------
+05:30

SQL> select sysdate,current_date from dual;

SYSDATE              CURRENT_DATE
-------------------- --------------------
15-APR-2010 06:33:48 15-APR-2010 19:03:49


Ok....i knew i misunderstood somewhere. Embarassed
So it takes the sessiontimezone. It still does not fit in the code posted above. The sessiontimezone is +5:30 but the current date takes +12:30 ?
Re: Difference between sysdate and current_date [message #451571 is a reply to message #451566] Thu, 15 April 2010 08:46 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's calculated from GMT. So current_date is right assuming you just ran that. Sysdate isn't in GMT.
Re: Difference between sysdate and current_date [message #451573 is a reply to message #451569] Thu, 15 April 2010 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You still didn't read the links I posted.
Quote:
SYSDATE returns the current date and time set for the operating system on which the database resides


Regards
Michel
Re: Difference between sysdate and current_date [message #451574 is a reply to message #451566] Thu, 15 April 2010 08:54 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
For a Movement even i was confused. but then I google

Quote:
more clarification i forgot to put that its OS time ... exact defination would be

SYSDATE returns the current date and time set for the operating system on which the database resides.
Re: Difference between sysdate and current_date [message #451575 is a reply to message #451574] Thu, 15 April 2010 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Why "would be"? It is exactly this as I quoted it from the documentation.

Regards
Michel
Re: Difference between sysdate and current_date [message #451576 is a reply to message #451566] Thu, 15 April 2010 09:02 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Yes Michel. That is quote form the Link Smile "Would be" May be that is the polite way to present "The Argument"
Re: Difference between sysdate and current_date [message #451578 is a reply to message #451571] Thu, 15 April 2010 09:20 Go to previous messageGo to next message
knw15pwr
Messages: 134
Registered: March 2010
Senior Member
Thanks cookiemonster and Michel.

@Michel - i did read the link. I failed to understand how it was implemented especially since DBTIMEZONE showed +00:00. So i assumed that the SESSIONTIMEZONE is something relative to this.

Which brings me another confusion -
Is the timezone in DBTIMEZONE different from the one that is reported by the command SYSTIMESTAMP ?

SQL> select systimestamp from dual;

SYSTIMESTAMP
----------------------------------------
15-APR-10 07.00.47.197235 AM -07:00

SQL> select dbtimezone from dual;

DBTIME
------
+00:00


@Rahul - yes i google'd. That is why my question was more specific -including the timezone.
Re: Difference between sysdate and current_date [message #451598 is a reply to message #451578] Thu, 15 April 2010 10:27 Go to previous message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
I meant both links and also the following one just beside the previous ones: SYSTIMESTAMP

Quote:
SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides.

You have the SYSTEM time zone not the DB time zone.

Regards
Michel

[Updated on: Thu, 15 April 2010 10:29]

Report message to a moderator

Previous Topic: ORA-01408
Next Topic: sum unique acct id's by groups of opty id's
Goto Forum:
  


Current Time: Thu Jun 12 16:16:55 CDT 2025