Oracle DATE data type

Learn how to convert a date in string format to a string Date format using TO_DATE function and Date literal formats. Also Learn how to get the current operating system time and current session DB time with example queries.

DATE– data type used to store the date(day,month,year) and time(Hours,minutes,seconds) values.

Date format of date data occupies fixed length of 7 bytes, each corresponding to century, year, month, day, hour, minute, and second to store date data.

The standard format is decided by the value of the NLS_DATE_FORMAT parameter.

SELECT
  value
FROM
  V$NLS_PARAMETERS
WHERE
  parameter = 'NLS_DATE_FORMAT';

Output

‘DD-MON-RR’

In our Oracle Database system, the value of NLS_DATE_FORMAT is ‘DD-MON-RR’.

TO Change the standard date format of oracle DB to ‘YYY-MM-DD’

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

Convert string to date

Oracle uses an internal format for storing the DATE data, you often have to convert a string to a date value before storing it in the date column.

To convert date values that are not in the standard format, you use the TO_DATE() function with a format string.

SELECT TO_DATE('2019-11-13','RRRR-MM-DD') FROM DUAL;

Output

11/13/2019

Let’s create a Date_demo table as shown below.

create table Date_demo
 (
   id  number,     
    start_date DATE
 );

TO_DATE() function – to convert a string to a date before inserting as shown in the following example

insert into Date_demo
values
(1,
 TO_DATE('2017-11-13','RRRR-MM-DD')

);

SELECT * FROM Date_demo;

Output

ID START_DATE

1 11/13/2017

Instead of TO_date you can also use Date value as literal with the following syntax

DATE ‘YYYY-MM-DD’

DATE ‘2019-11-13’

select DATE '2019-11-13 ' from dual;

Output
11/13/2019

Date literal in insertion statement as shown in below example.

insert into Date_demo
values
(2,
 DATE '2017-11-13 '
);

you can also use to_char function to change the date formats

select  TO_CHAR( sysdate, 'FMMonth DD, YYYY' )  start_date
from dual;

Output:
November 13, 2019

The following statement returns the current date with the standard date format by using the SYSDATE function.

select sysdate from dual;

Output

11/13/2019 11:54:01 AM

Date conversion formats

DATE functions in Oracle-to get the current date and time.

SESSIONTIMEZONE – function returns the time zone of the current session. (example GMT +5:30)

SELECT SESSIONTIMEZONE 
FROM DUAL;

Output

+05:30

SYSDATE – Return the current system date and time of the operating system where the Oracle Database resides.

CURRENT_DATE–Return the current date and time in the session time zone

Change the session time zone to get better understanding between sysdate and current_date functions.

Changing the timezone:

ALTER SESSION SET TIME_ZONE = '-07:00';

SELECT SESSIONTIMEZONE 
FROM DUAL;

Output

-07:00

SELECT CURRENT_DATE CDTISTZ,/*CURRENT_DATE_TIME_IN_SESSION_TIME_ZONE ,*/
       SYSDATE COSDTODR /*CURRENT_OPERATING_SYSTEM_DATE_TIME_OF_ORACLE_DATABASE_RESIDED */
  FROM DUAL;

Output

CDTISTZ			 COSDTODR
11/13/2019 1:58:43 AM,  11/13/2019 2:28:43 PM