How to Convert Strings to Datetime Data

In Oracle Database, converting strings to datetime data types is a crucial task. For example, converting date strings imported from external data, such as CSV files, into manipulable datetime data types makes analysis and management within the database easier.

This article explains how to convert strings to datetime data using the TO_DATE function in a way that is easy for beginners to understand. It also includes practical table operation examples, troubleshooting for errors, and helpful points for real-world use.

Basic Syntax of the TO_DATE Function
1. Argument Description
Format Model Details and Examples
Practical Example: Operations Using a Test Table
Practical Points for Real-World Use
Troubleshooting
1. Error 1: ORA-01861: literal does not match format string
Best Practices
Summary

Basic Syntax of the TO_DATE Function

The TO_DATE function is used to convert a string into a datetime data type (DATE type). The syntax is as follows:

TO_DATE(string, 'format_model')

Argument Description

string: The string data representing the datetime you want to convert.
format_model: The model used to specify the format of the string.

Example:

SELECT TO_DATE('2024-12-21 14:30:45', 'YYYY-MM-DD HH24:MI:SS') AS converted_date
FROM DUAL;

Result:

CONVERTED_DATE
-------------------
21-DEC-24 14:30:45

Format Model Details and Examples

A format model specifies how the date is written in its string form. Below are representative format models and their examples.

Format Model	Description	Sample Data
YYYY	4-digit year	2024
MM	Month (2-digit)	01 (January)
DD	Day (2-digit)	21
HH24	Hour (24-hour clock)	14
MI	Minute	30
SS	Second	45

Practical Example: Operations Using a Test Table

Below, we will create a test table, insert data, and attempt to convert it to datetime data.

1. Create the Table

Create a table to store datetimes as strings.

CREATE TABLE test_dates (
    id NUMBER PRIMARY KEY,
    date_string VARCHAR2(20)
);

2. Insert Data

Insert datetime string data in the following format.

INSERT INTO test_dates (id, date_string) 
VALUES (1, '2024-12-21 14:30:45');

INSERT INTO test_dates (id, date_string) 
VALUES (2, '2024-12-22 08:15:00');

Check the insertion results.

SELECT * FROM test_dates;

Result:

ID  DATE_STRING
--  --------------------
 1  2024-12-21 14:30:45
 2  2024-12-22 08:15:00

3. Convert Datetime Data Using TO_DATE Function

Next, we will use the TO_DATE function to convert the strings and retrieve them as datetime data.

Query Example 1: Basic Conversion

SELECT id, 
       date_string, 
       TO_DATE(date_string, 'YYYY-MM-DD HH24:MI:SS') AS converted_date
FROM test_dates
WHERE id = 1;

Result:

ID  DATE_STRING          CONVERTED_DATE
--  -------------------- -------------------
 1  2024-12-21 14:30:45  21-DEC-24 14:30:45

Query Example 2: Converting Different Datetime Data

SELECT id, 
       date_string, 
       TO_DATE(date_string, 'YYYY-MM-DD HH24:MI:SS') AS converted_date
FROM test_dates
WHERE id = 2;

Result:

ID  DATE_STRING          CONVERTED_DATE
--  -------------------- -------------------
 2  2024-12-22 08:15:00  22-DEC-24 08:15:00

Practical Points for Real-World Use

1. Check Data Format

When inserting data, ensure that the string format is consistent. Different formats can cause errors or unexpected results.

2. Troubleshooting

Errors such as ORA-01861 (literal does not match format string) occur when the format model does not match the data format. Double-check the format of the inserted data.

3. Performance Optimization

If you frequently use datetime data, it is recommended to store it as the DATE type from the beginning, rather than storing datetime strings. This avoids conversion overhead.

Troubleshooting

Error 1: ORA-01861: literal does not match format string

Cause: The format model does not match the format of the input data.
Solution: Check the data format and use the correct format model.

Example: Before fix

TO_DATE('2024-12-21 14:30:45', 'MM/DD/YYYY HH24:MI:SS')

Example: After fix

TO_DATE('2024-12-21 14:30:45', 'YYYY-MM-DD HH24:MI:SS')

Best Practices

Data Format StandardizationIf the data format is standardized, queries become simpler and errors are reduced.
Appropriate Format Model SpecificationUse the correct format model that corresponds to the data string.
Consider PerformanceIf necessary, store datetime data directly in a DATE type column to reduce conversion costs.

Summary

Using the TO_DATE function, you can accurately convert string data into datetime data. This article explained practical examples using a test table, countermeasures for errors, and key points for real-world application.

Let’s further improve your date data manipulation skills based on this knowledge!

[reference]
Oracle Database SQL Language Reference, 19c