{"id":2382,"date":"2018-06-06T21:33:57","date_gmt":"2018-06-07T04:33:57","guid":{"rendered":"https:\/\/database.guide\/?p=2382"},"modified":"2021-01-28T14:12:21","modified_gmt":"2021-01-28T04:12:21","slug":"convert-from-date-time-to-string-examples-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/convert-from-date-time-to-string-examples-in-sql-server\/","title":{"rendered":"CONVERT() from Date\/Time to String Examples in SQL Server"},"content":{"rendered":"

The\u00a0CONVERT()<\/code> function allows you to convert between data types. It’s similar to the CAST()<\/code> function, but one of the benefits of CONVERT()<\/code> is that, when you convert from a date\/time data type to a string, you can add an optional argument that specifies the style that you want the return value to be in. For example, you can have it returned as dd.mm.yyyy<\/em>, yyyy-mm-dd<\/em>, dd mon yyyy<\/em>, etc<\/p>\n

This article contains examples of the various styles you can return when converting a date\/time value to a string using the CONVERT()<\/code> function in SQL Server<\/a>.<\/p>\n

<\/p>\n

Basic Example<\/h2>\n

The default style when converting from the\u00a0datetime<\/strong> and smalldatetime<\/strong> data types is 0<\/code> and 100<\/code> (these represent the same style). Therefore, when you don’t provide a style (third parameter), this is how it’s styled:<\/p>\n

DECLARE @date datetime = GETDATE();\r\nSELECT\r\n    @date AS Original,\r\n    CONVERT(varchar, @date) AS Converted;<\/pre>\n
Result:<\/p>\n
+-------------------------+---------------------+\r\n| Original                | Converted           |\r\n|-------------------------+---------------------|\r\n| 2018-06-07 03:08:21.997 | Jun  7 2018  3:08AM |\r\n+-------------------------+---------------------+\r\n<\/pre>\n
However, you’ll get a different result if the original data type is not\u00a0datetime<\/strong> or\u00a0smalldatetime<\/strong>.<\/p>\n
If you need it to be returned in a different style, you’ll need to specify a third argument.<\/p>\n
Styles with Two Digit Years<\/h2>\n
Below are examples of the various values you can use to specify the style using a two digit year component.<\/p>\n
Styles 1 to 6<\/h3>\n
DECLARE @date datetime2 = '2018-06-07';\r\nSELECT\r\n    CONVERT(nvarchar(30), @date, 1) AS '1',\r\n    CONVERT(nvarchar(30), @date, 2) AS '2',\r\n    CONVERT(nvarchar(30), @date, 3) AS '3',\r\n    CONVERT(nvarchar(30), @date, 4) AS '4',\r\n    CONVERT(nvarchar(30), @date, 5) AS '5',\r\n    CONVERT(nvarchar(30), @date, 6) AS '6';<\/pre>\n
Result:<\/p>\n
+----------+----------+----------+----------+----------+-----------+\r\n| 1        | 2        | 3        | 4        | 5        | 6         |\r\n|----------+----------+----------+----------+----------+-----------|\r\n| 06\/07\/18 | 18.06.07 | 07\/06\/18 | 07.06.18 | 07-06-18 | 07 Jun 18 |\r\n+----------+----------+----------+----------+----------+-----------+\r\n<\/pre>\n
Styles 7 to 6<\/h3>\n
DECLARE @date datetime2 = '2018-06-07';\r\nSELECT\r\n    CONVERT(nvarchar(30), @date, 7) AS '7',\r\n    CONVERT(nvarchar(30), @date, 8) AS '8',\r\n    CONVERT(nvarchar(30), @date, 10) AS '10',\r\n    CONVERT(nvarchar(30), @date, 11) AS '11',\r\n    CONVERT(nvarchar(30), @date, 12) AS '12',\r\n    CONVERT(nvarchar(30), @date, 14) AS '14';<\/pre>\n
Result:<\/p>\n
+------------+----------+----------+----------+--------+------------------+\r\n| 7          | 8        | 10       | 11       | 12     | 14               |\r\n|------------+----------+----------+----------+--------+------------------|\r\n| Jun 07, 18 | 00:00:00 | 06-07-18 | 18\/06\/07 | 180607 | 00:00:00.0000000 |\r\n+------------+----------+----------+----------+--------+------------------+\r\n<\/pre>\n
Styles with Four Digit Years<\/h2>\n
Below are examples of the various values you can use to specify the style using a four digit year component.<\/p>\n
Styles 100 to 103<\/h3>\n
DECLARE @date datetime2 = '2018-06-07 02:35:52.8537677';\r\nSELECT\r\n    CONVERT(nvarchar(30), @date, 100) AS '100',\r\n    CONVERT(nvarchar(30), @date, 101) AS '101',\r\n    CONVERT(nvarchar(30), @date, 102) AS '102',\r\n    CONVERT(nvarchar(30), @date, 103) AS '103';<\/pre>\n
Result:<\/p>\n
+---------------------+------------+------------+------------+\r\n| 100                 | 101        | 102        | 103        |\r\n|---------------------+------------+------------+------------|\r\n| Jun  7 2018  2:35AM | 06\/07\/2018 | 2018.06.07 | 07\/06\/2018 |\r\n+---------------------+------------+------------+------------+\r\n<\/pre>\n
Styles 104 to 108<\/h3>\n
DECLARE @date datetime2 = '2018-06-07 02:35:52.8537677';\r\nSELECT\r\n    CONVERT(nvarchar(30), @date, 104) AS '104',\r\n    CONVERT(nvarchar(30), @date, 105) AS '105',\r\n    CONVERT(nvarchar(30), @date, 106) AS '106',\r\n    CONVERT(nvarchar(30), @date, 107) AS '107',\r\n    CONVERT(nvarchar(30), @date, 108) AS '108';<\/pre>\n
Result:<\/p>\n
+------------+------------+-------------+--------------+----------+\r\n| 104        | 105        | 106         | 107          | 108      |\r\n|------------+------------+-------------+--------------+----------|\r\n| 07.06.2018 | 07-06-2018 | 07 Jun 2018 | Jun 07, 2018 | 02:35:52 |\r\n+------------+------------+-------------+--------------+----------+\r\n<\/pre>\n
Styles 109 to 112<\/h3>\n
DECLARE @date datetime2 = '2018-06-07 02:35:52.8537677';\r\nSELECT\r\n    CONVERT(nvarchar(30), @date, 109) AS '109',\r\n    CONVERT(nvarchar(30), @date, 110) AS '110',\r\n    CONVERT(nvarchar(30), @date, 111) AS '111',\r\n    CONVERT(nvarchar(30), @date, 112) AS '112';<\/pre>\n
Result:<\/p>\n
+--------------------------------+------------+------------+----------+\r\n| 109                            | 110        | 111        | 112      |\r\n|--------------------------------+------------+------------+----------|\r\n| Jun  7 2018  2:35:52.8537677AM | 06-07-2018 | 2018\/06\/07 | 20180607 |\r\n+--------------------------------+------------+------------+----------+\r\n<\/pre>\n
Styles 113 to 114<\/h3>\n
DECLARE @date datetime2 = '2018-06-07 02:35:52.8537677';\r\nSELECT\r\n    CONVERT(nvarchar(30), @date, 113) AS '113',\r\n    CONVERT(nvarchar(30), @date, 114) AS '114';<\/pre>\n
Result:<\/p>\n
+------------------------------+------------------+\r\n| 113                          | 114              |\r\n|------------------------------+------------------|\r\n| 07 Jun 2018 02:35:52.8537677 | 02:35:52.8537677 |\r\n+------------------------------+------------------+\r\n<\/pre>\n
Styles 120 to 127<\/h3>\n
DECLARE @date datetime2 = '2018-06-07';\r\nSELECT    \r\n    CONVERT(nvarchar(30), @date, 120) AS '120',\r\n    CONVERT(nvarchar(30), @date, 126) AS '126',\r\n    CONVERT(nvarchar(30), @date, 127) AS '127';<\/pre>\n
Result:<\/p>\n
+---------------------+---------------------+---------------------+\r\n| 120                 | 126                 | 127                 |\r\n|---------------------+---------------------+---------------------|\r\n| 2018-06-07 00:00:00 | 2018-06-07T00:00:00 | 2018-06-07T00:00:00 |\r\n+---------------------+---------------------+---------------------+\r\n<\/pre>\n
Style 130<\/h3>\n
DECLARE @date datetime2 = '2018-06-07';\r\nSELECT    \r\n    CONVERT(nvarchar(30), @date, 130) AS '130';<\/pre>\n
Result:<\/p>\n
\r\n+--------------------------------+\r\n| 130                            |\r\n|--------------------------------|\r\n| 24 \u0631\u0645\u0636\u0627\u0646 1439 12:00:00.0000000 |\r\n+--------------------------------+\r\n<\/pre>\n
Microsoft warns that this value does not render correctly on a default US installation of SSMS<\/a>.<\/p>\n
Style 131<\/h3>\n
DECLARE @date datetime2 = '2018-06-07';\r\nSELECT    \r\n    CONVERT(nvarchar(30), @date, 131) AS '131';<\/pre>\n
Result:<\/p>\n
\r\n+-------------------------------+\r\n| 131                           |\r\n|-------------------------------|\r\n| 24\/09\/1439 12:00:00.0000000AM |\r\n+-------------------------------+\r\n<\/pre>\n
You can read more about the available date\/time styles on the Microsoft website<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
The\u00a0CONVERT() function allows you to convert between data types. It’s similar to the CAST() function, but one of the benefits of CONVERT() is that, when you convert from a date\/time data type to a string, you can add an optional argument that specifies the style that you want the return value to be in. For … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[72,116,68,115,93,77,61],"class_list":["post-2382","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-conversion-functions","tag-convert","tag-date-functions","tag-dates","tag-functions","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2382","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2382"}],"version-history":[{"count":13,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2382\/revisions"}],"predecessor-version":[{"id":5016,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2382\/revisions\/5016"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2382"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2382"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2382"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}