My Oracle Support Banner

Dates & Calendars - Frequently Asked Questions (Doc ID 227334.1)

Last updated on FEBRUARY 03, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
Oracle Database - Standard Edition - Version 8.0.3.0 and later
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2016***

Purpose

This note covers the FAQ for DATEs and Calendars in the Oracle database.
Please note that TIMESTAMPs and timezones are covered separately in: <Note:340512.1> Timestamps & timezones - Frequently Asked Questions

If you have a "wrong database time" (sysdate)  please see first of all <note 1627439.1> How to diagnose wrong time ( SYSDATE and SYSTIMESTAMP) after DST change , server reboot , database restart or installation when connecting to a database on an Unix server .

For the main Globalization (NLS) FAQ overview please see: <Note 60134.1> Globalization (NLS) - Frequently Asked Questions

Questions and Answers

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Questions and Answers
 1) Why is my setting of NLS_DATE_FORMAT in the init.ora not used?
 2) How should the default date format be changed or set?
 3) What is the difference between the YY and RR format masks?
 4) What is the difference between the YYYY and RRRR format masks?
 5) Why does Oracle allows date who are not exactly the same as the format mask?
 6) Where can I find a list and explanation of all possible date format masks?
 7) How does Oracle store the DATE datatype internally?
 8) Why is my SYSDATE / SYSTIMESTAMP time not the same as my system clock on Windows ?
 9) Why is my SYSDATE / SYSTIMESTAMP time not the same as my system clock on Unix?
 10)Why is my SYSDATE / SYSTIMESTAMP time not the same as my system clock on VMS?
 11)How do I see & set the TZ environment variable in a RAC environment
 12) How to see and define the timezone when using Grid Infrastructure (start the database using crsctl )?
 13) How to define the timezone for ASM logging (including ASM messages in the alert.log of the database)?
 14) How do I set up 2 databases with a different SYSDATE / SYSTIMESTAMP time on the same server?
 15) How do I set up 1 database with a different SYSDATE / SYSTIMESTAMP time for a 2 different schema's / users?
 16) My Alert.log has wrong time for some or all entry's.
 17) How does week numbering work & why do (some) years have 53 weeks?
 18 )How can I switch and convert between Calendars?
 19 )Where to find (Oracle) information on non-western Calendars ?
 20) Where can I find more external information about calendars?
 21) A complete overview of the TZ (sysdate) behavior on Unix platforms:
 22) Oracle RDBMS SYSDATE / SYSTIMESTAMP and DST changes.
 23) sysdate remains the same / is different from systimestamp
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.