Tutorial by Examples

All DATEs have a time component; however, it is customary to store dates which do not need to include time information with the hours/minutes/seconds set to zero (i.e. midnight). Use an ANSI DATE literal (using ISO 8601 Date format): SELECT DATE '2000-01-01' FROM DUAL; Convert it from a string ...

Convert it from a string literal using TO_DATE(): SELECT TO_DATE( '2000-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS' ) FROM DUAL; Or use a TIMESTAMP literal: CREATE TABLE date_table( date_value DATE ); INSERT INTO date_table ( date_value ) VALUES ( TIMESTAMP '2000-01-01 12:00:00' ); Oracl...

In Oracle a DATE data type does not have a format; when Oracle sends a DATE to the client program (SQL/Plus, SQL/Developer, Toad, Java, Python, etc) it will send 7- or 8- bytes which represent the date. A DATE which is not stored in a table (i.e. generated by SYSDATE and having "type 13" ...

Use TO_CHAR( date [, format_model [, nls_params]] ): (Note: if a format model is not provided then the NLS_DATE_FORMAT session parameter will be used as the default format model; this can be different for every session so should not be relied on. It is good practice to always specify the format mod...

When Oracle implicitly converts from a DATE to a string or vice-versa (or when TO_CHAR() or TO_DATE() are explicitly called without a format model) the NLS_DATE_FORMAT session parameter will be used as the format model in the conversion. If the literal does not match the format model then an excepti...

When SQL/Plus or SQL Developer display dates they will perform an implicit conversion to a string using the default date format model (see the Setting the Default Date Format Model example). You can change how a date is displayed by changing the NLS_DATE_FORMAT parameter.

In oracle, the difference (in days and/or fractions thereof) between two DATEs can be found using subtraction: SELECT DATE '2016-03-23' - DATE '2015-12-25' AS difference FROM DUAL; Outputs the number of days between the two dates: DIFFERENCE ---------- 89 And: SELECT TO_DATE( '201...

The difference in months between two dates can be found using the MONTHS_BETWEEN( date1, date2 ): SELECT MONTHS_BETWEEN( DATE '2016-03-10', DATE '2015-03-10' ) AS difference FROM DUAL; Outputs: DIFFERENCE ---------- 12 If the difference includes part months then it will return the ...

The year, month or day components of a DATE data type can be found using the EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue ) SELECT EXTRACT (YEAR FROM DATE '2016-07-25') AS YEAR, EXTRACT (MONTH FROM DATE '2016-07-25') AS MONTH, EXTRACT (DAY FROM DATE '2016-07-25') AS DAY FROM D...

The DATE data type does not handle time zones or changes in daylight savings time. Either: use the TIMESTAMP WITH TIME ZONE data type; or handle the changes in your application logic. A DATE can be stored as Coordinated Universal Time (UTC) and converted to the current session time zone like...

Oracle does not handle leap seconds. See My Oracle Support note 2019397.2 and 730795.1 for more details.

You can use TO_CHAR( date_value, 'D' ) to get the day-of-week. However, this is dependent on the NLS_TERRITORY session parameter: ALTER SESSION SET NLS_TERRITORY = 'AMERICA'; -- First day of week is Sunday SELECT TO_CHAR( DATE '1970-01-01', 'D' ) FROM DUAL; Outputs 5 ALTER SESSION SET ...