Implicit vs Explicit Date Conversion

View All Scripts Login to Run Script

Script Name Implicit vs Explicit Date Conversion
Description Discuss the difference between implicit and explicit date conversion using the to_date function.
Area SQL General / Data Manipulation
Contributor NepoBot
Created Friday November 02, 2018

Statement 1
Create dates table with a single column called "the_date" with a data type of date
Create Table
```
create table dates(  
  the_date date  
)
```
```
Table created.
```
Statement 2
Insert a record into the dates table without specifying any column names. Oracle also converts our string value "01-JAN-05" into a date.
Implicit date conversion
```
insert into dates values ('01-JAN-05')
```
```
1 row(s) inserted.
```
Statement 3
Insert a record by explicitly stating the order in which we will provide the column values. This is preferred as it helps prevent issues in the future as the table is modified or changed. Always provide the list of columns! Always!
Specify Column Order
```
insert into dates( the_date ) values ('01-JAN-06')
```
```
1 row(s) inserted.
```
Statement 4
Oracle automatically converted the string value "01-JAN-05" to a date because it just so happens Oracle thinks strings in that format are dates because it matches the database NLS_DATE_FORMAT setting. DD-MON-RR is the default date format for an Oracle database.
NLS Date Format
```
select parameter, value   
from nls_session_parameters  
where parameter = 'NLS_DATE_FORMAT'
```
```
PARAMETER VALUE
NLS_DATE_FORMAT DD-MON-RR
```
Statement 5
Change the date format for this session so we can enter dates in a different format for the entire session.
Change Session Date Format
```
alter session set nls_date_format = 'MM/DD/RR'
```
```
Statement processed.
```
Statement 6
Here we can see that the session as indeed updated and we now have a new format mask for dates. Note this is only for our session, this has zero impact on other users in the database!
Verify Session Was altered
```
select value  
from nls_session_parameters  
where parameter = 'NLS_DATE_FORMAT'
```
```
VALUE
MM/DD/RR
```
Statement 7
Oracle implicitly converts the new date format for us.
Date Conversion
```
insert into dates( the_date ) values ('01/01/06')
```
```
1 row(s) inserted.
```
Statement 8
if we do not wish to alter the current session format but would like to use a different date format as an input then we run that string value through the to_date function with the format mask we are providing as the second parameter. to_date( [date value], [format mask] )
Explicit Conversion using the TO_DATE function
```
insert into dates( the_date ) values ( to_date('01-JAN-07','DD-MON-RR') )
```
```
1 row(s) inserted.
```

PARAMETER	VALUE
NLS_DATE_FORMAT	DD-MON-RR

VALUE
MM/DD/RR

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database