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.
