Dates and strings are some of the most common data types you will interact with when working with databases. It is, therefore, no surprise that you will encounter a scenario where you need to convert a given string literal into a date type.
Let us dive in.
Orale to_date() Function
The to_date() function in Oracle allows you to convert a given string date literal into a date type.
The function syntax can be expressed in the snippet below:
TO_DATE(char [, fmt [, 'nlsparam' ] ])The function accepts three main arguments:
char– the first argument is thechar, which defines the input date string. The input value can beCHAR,VARCHAR2,NCHAR, orNVARCHAR2.fmt– The second parameter isfmt. This parameter defines the datetime model format of the input value. You can skip this parameter if the input value follows the default format ofDD-MON-YY, for example,01-JAN-2024.
NOTE: If the format is Julian, represented as J, then the input value must be an integer type.
The input value can be comprised of the following formats.
| Format Model | Description |
|---|---|
YYYY | 4-digit year (e.g., 2024) |
YY | 2-digit year (e.g., 24 for 2024) |
MM | 2-digit month (01 to 12) |
MON | Abbreviated month name (e.g., Jan, Feb, Mar) |
MONTH | Full month name (e.g., January, February) |
DD | 2-digit day of the month (01 to 31) |
HH | Hour in 12-hour format (01 to 12) |
HH12 | Hour in 12-hour format (01 to 12) |
HH24 | Hour in 24-hour format (00 to 23) |
MI | Minute (00 to 59) |
SS | Second (00 to 59) |
AM | Meridian indicator (AM or PM) |
PM | Meridian indicator (AM or PM) |
AD | Anno Domini (AD) or Before Christ (BC) |
BC | Before Christ (BC) |
TZD | Time Zone Abbreviation (e.g., PST, GMT) |
TZO | Time Zone Offset (e.g., -08:00) |
DY | Abbreviated day name (e.g., Sun, Mon, Tue) |
DAY | Full day name (e.g., Sunday, Monday) |
WW | Week of the year (1 to 52/53) |
IW | ISO week of the year (1 to 52/53) |
DDD | Day of the year (1 to 366) |
YYYY | 4-digit year (e.g., 2024) |
YY | 2-digit year (e.g., 24 for 2024) |
CC | Century (e.g., 20 for the 21st century) |
SYYYY | 4-digit year (e.g., 2024) |
SYY | 2-digit year (e.g., 24 for 2024) |
SYYYY | 4-digit year including century (e.g., 2024) |
SYY | 2-digit year including century (e.g., 24 for 2024) |
nlsparam– Finally, the nlsparam parameter is used to specify the language for data and month in the string. The format isNLS_DATE_FORMAT = language. Keep in mind that this is an optional parameter, and Oracle will default to the default language of your session.
Example Function Usage
The following are examples of the to_date function in the Oracle database.
Example 1 – Basic Conversion
The example below demonstrates how to use the to_date function to convert a given character string to a date.
select to_date('January 10 2024', 'Month dd, YYYY')
from dual;In this case, we convert the given date string to a date type using the Month dd, YYYY format.
The resulting value is shown:
TO_DATE('JANUARY102023','MONTHDD,YYYY')
2024-01-10Example 2 – Convert Date and Time to Date Type
The second example illustrates how to convert a given character string to date.
select to_date('January 10, 2024, 1:03', 'Month dd, YYYY, HH:MI P.M.')
from dual;In this case, we need to specify the time format as HH:MI P.M.
The resulting output is as shown:
TO_DATE('JANUARY10,2024,1:03','MONTHDD,YYYY,HH:MIP.M.')
2024-01-10 01:03:00Example 3 – Specifying the Language Parameter
Consider the example below demonstrating how to use the to_date function with the nls_date_language parameter.
select to_date('January 10, 2024, 1:03', 'Month dd, YYYY, HH:MI P.M.', 'nls_date_language=American')
from dual;This should return the value:
2024-01-10 01:03:00Once we change the session language as:
alter session set nls_territory = 'Australia';Convert the time.
select to_date('January 10, 2024, 1:03', 'Month dd, YYYY, HH:MI P.M.', 'nls_date_language=American')
from dual;Ending
In this post, we explore the usage of the to_date function in the Oracle database to convert a given input date literal to date type.
