SQL Convert String to Date Functions

Summary: in this tutorial, you will learn various functions that convert a string to a date in SQL.

Convert string to date using CAST() function

SQL provides a CAST() function that allows you to convert a string to a date. The following illustrates the syntax of the CAST() function:

CAST (string AS DATE)   
Code language: SQL (Structured Query Language) (sql)

In this syntax, the string can be any DATE value that is convertible to a date. The CAST() function returns a DATE value if it successfully converts the string to date. In case the function fails to convert, it issues an error, depending on the implementation of a specific database system.

The following example shows how to convert a string to a date:

SELECT CAST('2018' AS DATE);
Code language: SQL (Structured Query Language) (sql)

SQL Server converts the string 2018 to January 1st, 2018:

2018-01-01
Code language: SQL (Structured Query Language) (sql)

Similarly, the following example also converts the string 180101 to January 1st, 2018:

SELECT CAST('180101' AS DATE);
Code language: SQL (Structured Query Language) (sql)

Here is the result:

2018-01-01Code language: SQL (Structured Query Language) (sql)

Convert string to date using TO_DATE() function

Oracle and PostgreSQL provide the TO_DATE() function that converts a string to date based on a specified format.

The following shows the TO_DATE() function syntax:

TO_DATE(string, format)
Code language: SQL (Structured Query Language) (sql)

For example, to convert the string '10 Aug 2018' to a date value, you use the following statement:

SELECT
  TO_DATE( '10 Aug 2018', 'DD MON YYYY' )
FROM
  dual; 
Code language: SQL (Structured Query Language) (sql)

Notice that the date format must be corresponding to the date string as specified in the statement DD MON YYYY

Check it out the Oracle TO_DATE() and PostgreSQL TO_DATE() functions for the details.

In this tutorial, you have learned how to use the CAST() and TO_DATE() functions to convert a string to a date in SQL.