{"id":1550,"date":"2018-07-21T05:57:48","date_gmt":"2018-07-21T12:57:48","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1550"},"modified":"2020-04-04T06:56:16","modified_gmt":"2020-04-04T13:56:16","slug":"sql-convert-string-to-date-functions","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-date-functions\/sql-convert-string-to-date-functions\/","title":{"rendered":"SQL Convert String to Date Functions"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn various functions that convert a string to a date in SQL.<\/p>\n\n\n\n

Convert string to date using CAST() function #<\/a><\/h2>\n\n\n\n

SQL provides a CAST()<\/code> function that allows you to convert a string to a date. The following illustrates the syntax of the CAST()<\/code> function:<\/p>\n\n\n

CAST (string AS DATE)   \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the string<\/code> can be any DATE<\/code> value that is convertible to a date. The CAST()<\/code> function returns a DATE<\/code> 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.<\/p>\n\n\n\n
The following example shows how to convert a string to a date:<\/p>\n\n\n
SELECT<\/span> CAST<\/span>('2018'<\/span> AS<\/span> DATE<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server converts the string 2018<\/code> to January 1st, 2018<\/code>:<\/p>\n\n\n
2018-01-01\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Similarly, the following example also converts the string 180101<\/code> to January 1st, 2018<\/code>:<\/p>\n\n\n
SELECT<\/span> CAST<\/span>('180101'<\/span> AS<\/span> DATE<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result:<\/p>\n\n\n
2018-01-01<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Convert string to date using TO_DATE() function #<\/a><\/h2>\n\n\n\n
Oracle and PostgreSQL provide the TO_DATE()<\/code> function that converts a string to date based on a specified format.<\/p>\n\n\n\n
The following shows the TO_DATE()<\/code> function syntax:<\/p>\n\n\n
TO_DATE(string, format)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, to convert the string '10 Aug 2018'<\/code> to a date value, you use the following statement:<\/p>\n\n\n
SELECT<\/span>\n  TO_DATE<\/span>( '10 Aug 2018'<\/span>, 'DD MON YYYY'<\/span> )\nFROM<\/span>\n  dual; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the date format must be corresponding to the date string as specified in the statement DD MON YYYY<\/code><\/p>\n\n\n\n
In this tutorial, you have learned how to use the CAST()<\/code> and TO_DATE()<\/code> functions to convert a string to a date in SQL.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Check it out the Oracle TO_DATE()<\/a><\/code> and PostgreSQL TO_DATE()<\/a><\/code> functions for the details.<\/p>\n\n\n\n