{"id":22517,"date":"2021-12-17T08:46:17","date_gmt":"2021-12-16T22:46:17","guid":{"rendered":"https:\/\/database.guide\/?p=22517"},"modified":"2023-01-24T18:23:58","modified_gmt":"2023-01-24T08:23:58","slug":"convert-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/convert-in-sql-server\/","title":{"rendered":"CONVERT() in SQL Server"},"content":{"rendered":"\n

In SQL Server, the CONVERT()<\/code> function converts an expression of one data type to another.<\/p>\n\n\n\n

Here’s a quick overview of the function with examples.<\/p>\n\n\n\n\n\n\n\n

Syntax<\/h2>\n\n\n\n

The syntax goes like this:<\/p>\n\n\n\n

CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) <\/code><\/pre>\n\n\n\n
Where expression<\/code> is the expression to convert, data_type<\/code> is the new data type, and length<\/code> is an optional length for the new data type.<\/p>\n\n\n\n
The optional style<\/code> argument can be used to specify how the function should translate the expression<\/code> argument. For example, you could use this argument to specify the date format.<\/p>\n\n\n\n
Example<\/h2>\n\n\n\n
Here’s an example of converting a string to decimal:<\/p>\n\n\n\n
SELECT CONVERT(DECIMAL(5,2), '007');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
7.00<\/pre>\n\n\n\n
String to Date<\/h2>\n\n\n\n
Here’s an example of casting a string to a date:<\/p>\n\n\n\n
SELECT CONVERT(date, '09 Feb 2030');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
2030-02-09<\/pre>\n\n\n\n
In this case the CONVERT()<\/code> function was able to determine which date parts are which because I provided the date in a format that it recognises. <\/p>\n\n\n\n
Passing a value that can’t be cast as a date results in an error:<\/p>\n\n\n\n
SELECT CONVERT(date, 'My Birthday');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
Msg 241, Level 16, State 1, Line 1\nConversion failed when converting date and\/or time from character string.<\/pre>\n\n\n\n
Locale<\/h2>\n\n\n\n
The language of the current session can make a difference in how dates are interpreted.<\/p>\n\n\n\n
Here’s what happens when using the British language:<\/p>\n\n\n\n
SET LANGUAGE British;\nSELECT CONVERT(date, '09\/02\/2030');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
2030-02-09<\/pre>\n\n\n\n
In this case, the date was interpreted as being the ninth day of February.<\/p>\n\n\n\n
Let’s change the language to us_English<\/code>:<\/p>\n\n\n\n
SET LANGUAGE us_English;\nSELECT CONVERT(date, '09\/02\/2030');<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
2030-09-02<\/pre>\n\n\n\n
This time it interpreted the date as being the second day of September.<\/p>\n\n\n\n
The style<\/em><\/code> Argument<\/h2>\n\n\n\n
We can use the optional style<\/em><\/code> argument to specify how the expression should be translated.<\/p>\n\n\n\n
Example:<\/p>\n\n\n\n
SET LANGUAGE British;\nSELECT \n    CONVERT(date, '09\/02\/2030') AS \"British\",\n    CONVERT(date, '09\/02\/2030', 101) AS \"US\",\n    CONVERT(date, '09\/02\/30', 1) AS \"US (short)\",\n    CONVERT(date, '20300902', 112) AS \"ISO\",\n    CONVERT(date, '09.02.2030', 104) AS \"German\";<\/code><\/pre>\n\n\n\n
Result:<\/p>\n\n\n\n
+------------+------------+--------------+------------+------------+\n| British    | US         | US (short)   | ISO        | German     |\n|------------+------------+--------------+------------+------------|\n| 2030-02-09 | 2030-09-02 | 2030-09-02   | 2030-09-02 | 2030-02-09 |\n+------------+------------+--------------+------------+------------+<\/pre>\n\n\n\n
Valid styles:<\/p>\n\n\n\n