{"id":1851,"date":"2019-04-24T16:43:57","date_gmt":"2019-04-24T09:43:57","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1851"},"modified":"2020-04-11T20:12:14","modified_gmt":"2020-04-11T13:12:14","slug":"convert-string-to-datetime","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-system-functions\/convert-string-to-datetime\/","title":{"rendered":"Convert String to Datetime"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to convert a string to a datetime in SQL Server using the CONVERT()<\/code> and TRY_CONVERT()<\/code> function.<\/p>\n\n\n\n

Introduction to CONVERT() and TRY_CONVERT() functions #<\/a><\/h2>\n\n\n\n

SQL Server provides the CONVERT()<\/a><\/code> function that converts a value of one type to another:<\/p>\n\n\n

CONVERT(target_type, expression [, style])\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Besides the CONVERT()<\/code> function, you can also use the TRY_CONVERT()<\/code> function:<\/p>\n\n\n
TRY_CONVERT(target_type, expression [, style])\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The main difference between CONVERT()<\/code> and TRY_CONVERT()<\/code> is that in case of conversion fails, the CONVERT()<\/code> function raises an error while the TRY_CONVERT()<\/code> function returns NULL.<\/p>\n\n\n\n
This example uses the CONVERT()<\/code> function to convert a string in ANSI date format to a datetime:<\/p>\n\n\n
  SELECT<\/span> \n    CONVERT<\/span>(DATETIME, '2019-08-15'<\/span>, 102<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
result\n-----------------------<\/span>\n2019-08-15 00:00:00.000\n\n(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the conversion fails, the CONVERT()<\/code> function will raise an error:<\/p>\n\n\n
SELECT<\/span> \n    CONVERT<\/span>(DATETIME, '2019-18-15'<\/span>, 102<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following is the error message:<\/p>\n\n\n
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The TRY_CONVERT()<\/code> function, on the other hand, returns NULL instead of raising an error if the conversion fails:<\/p>\n\n\n
SELECT<\/span> \n    TRY_CONVERT<\/span>(DATETIME, '2019-18-15'<\/span>, 102<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is:<\/p>\n\n\n
result\n-----------------------<\/span>\nNULL\n\n(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Converting a string in ANSI\/ISO and US date format to a datetime #<\/a><\/h2>\n\n\n\n
Both CONVERT()<\/code> and TRY_CONVERT()<\/code> function can recognize ANSI\/ISO and US formats with various delimiters by default so you don’t have to add the style<\/code> parameter.<\/p>\n\n\n\n
This example shows how to use the CONVERT()<\/code> function to convert strings in ISO date format to datetime values:<\/p>\n\n\n
SELECT<\/span> CONVERT<\/span>(DATETIME, '2019-09-25'<\/span>);\nSELECT<\/span> CONVERT<\/span>(DATETIME, '2019\/09\/25'<\/span>);\nSELECT<\/span> CONVERT<\/span>(DATETIME, '2019.09.25'<\/span>);\nSELECT<\/span> CONVERT<\/span>(DATETIME, '2019-09-25 12:11'<\/span>);\nSELECT<\/span> CONVERT<\/span>(DATETIME, '2019-09-25 12:11:09'<\/span>);\nSELECT<\/span> CONVERT<\/span>(DATETIME, '2019-09-25 12:11:09.555'<\/span>);\nSELECT<\/span> CONVERT<\/span>(DATETIME, '2019\/09\/25 12:11:09.555'<\/span>);\nSELECT<\/span> CONVERT<\/span>(DATETIME, '2019.09.25 12:11:09.555'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the CONVERT()<\/code> function can also convert an ISO date string without delimiters to a date value as shown in the following example:<\/p>\n\n\n
SELECT<\/span> \n    CONVERT<\/span>(DATETIME, '20190731'<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
result\n-----------------------<\/span>\n2019-07-31 00:00:00.000\n\n(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The CONVERT()<\/code> and TRY_CONVERT()<\/code> functions can convert United States datetime format (month, day, year and time) by default, therefore, you don’t need to specify style 101:<\/p>\n\n\n
SELECT<\/span> TRY_CONVERT<\/span>( DATETIME, '12-31-2019'<\/span>);\nSELECT<\/span> TRY_CONVERT<\/span>( DATETIME, '12\/31\/2019'<\/span>);\nSELECT<\/span> TRY_CONVERT<\/span>( DATETIME, '12.31.2019'<\/span>);\nSELECT<\/span> TRY_CONVERT<\/span>( DATETIME, '12-31-2019 12:15'<\/span>);\nSELECT<\/span> TRY_CONVERT<\/span>( DATETIME, '12\/31\/2019 12:15:10'<\/span>);\nSELECT<\/span> TRY_CONVERT<\/span>( DATETIME, '12.31.2019 12:15:10.333'<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to convert a string to a datetime using the CONVERT()<\/code> and TRY_CONVERT()<\/code> functions.<\/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