Microsoft SQL Server Dates Date & Time Formatting using CONVERT
Example
You can use the CONVERT function to cast a datetime datatype to a formatted string.
SELECT GETDATE() AS [Result] -- 2016-07-21 07:56:10.927
You can also use some built-in codes to convert into a specific format. Here are the options built into SQL Server:
DECLARE @convert_code INT = 100 -- See Table Below SELECT CONVERT(VARCHAR(30), GETDATE(), @convert_code) AS [Result]
| @convert_code | Result |
|---|---|
| 100 | "Jul 21 2016 7:56AM" |
| 101 | "07/21/2016" |
| 102 | "2016.07.21" |
| 103 | "21/07/2016" |
| 104 | "21.07.2016" |
| 105 | "21-07-2016" |
| 106 | "21 Jul 2016" |
| 107 | "Jul 21, 2016" |
| 108 | "07:57:05" |
| 109 | "Jul 21 2016 7:57:45:707AM" |
| 110 | "07-21-2016" |
| 111 | "2016/07/21" |
| 112 | "20160721" |
| 113 | "21 Jul 2016 07:57:59:553" |
| 114 | "07:57:59:553" |
| 120 | "2016-07-21 07:57:59" |
| 121 | "2016-07-21 07:57:59.553" |
| 126 | "2016-07-21T07:58:34.340" |
| 127 | "2016-07-21T07:58:34.340" |
| 130 | "16 ???? 1437 7:58:34:340AM" |
| 131 | "16/10/1437 7:58:34:340AM" |
SELECT GETDATE() AS [Result] -- 2016-07-21 07:56:10.927
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),100) AS [Result] -- Jul 21 2016 7:56AM
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),101) AS [Result] -- 07/21/2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),102) AS [Result] -- 2016.07.21
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),103) AS [Result] -- 21/07/2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),104) AS [Result] -- 21.07.2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS [Result] -- 21-07-2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),106) AS [Result] -- 21 Jul 2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),107) AS [Result] -- Jul 21, 2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),108) AS [Result] -- 07:57:05
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),109) AS [Result] -- Jul 21 2016 7:57:45:707AM
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS [Result] -- 07-21-2016
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),111) AS [Result] -- 2016/07/21
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),112) AS [Result] -- 20160721
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS [Result] -- 21 Jul 2016 07:57:59:553
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS [Result] -- 07:57:59:553
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),120) AS [Result] -- 2016-07-21 07:57:59
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),121) AS [Result] -- 2016-07-21 07:57:59.553
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),126) AS [Result] -- 2016-07-21T07:58:34.340
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),127) AS [Result] -- 2016-07-21T07:58:34.340
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),130) AS [Result] -- 16 ???? 1437 7:58:34:340AM
UNION SELECT CONVERT(VARCHAR(30),GETDATE(),131) AS [Result] -- 16/10/1437 7:58:34:340AM
