Converting a datetime to a string value is a common requirement.
Use CONVERT or TRY_CONVERT to perform this operation.
This example converts the current datetime to a string.
SELECT CONVERT(VARCHAR, GETDATE()) AS String
The VARCHAR argument specifies that the output is a string value.
| String |
|---|
| Dec 21 2023 04:23PM |
The CONVERT function can convert datetime to string values.
Optionally, the date format can be specified with a style argument (see below for options).
The style argument is mostly used to display the datetime in a specific format.
Syntax of the CONVERT function.
CONVERT(varchar, datetime-value [, style])
varchar -- specifies that the datetime value will be converted to a string value.
datetime-value -- the datetime value to be converted.
style -- optional, the format of the date to be returned.
These are valid style values with associated datetime string formats.
| Style | Description | Format |
|---|---|---|
0 or 100 |
Default | mon dd yyyy hh:miAM/PM |
1 or 101 |
U.S. |
1 = mm/dd/yy 101 = mm/dd/yyyy |
2 or 102 |
ANSI |
2 = yy.mm.dd 102 = yyyy.mm.dd |
3 or 103 |
British/French |
3 = dd/mm/yy 103 = dd/mm/yyyy |
4 or 104 |
German |
4 = dd.mm.yy 104 = dd.mm.yy |
5 or 105 |
Italian |
5 = dd-mm-yy 105 = dd-mm-yyyy |
6 or 106 |
- |
6 = dd mon yy 106 = dd mon yy |
7 or 107 |
- |
7 = Mon dd, yy 107 = Mon dd, yyyy |
8 or 108 |
- | hh:mi:ss |
9 or 109 |
Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM/PM |
10 or 110 |
USA |
10 = mm-dd-yy 110 = mm-dd-yyyy |
11 or 111 |
JAPAN |
11 = yy/mm/dd 111 = yyyy/mm/dd |
12 or 112 |
ISO |
12 = yymmdd 112 = yyyymmdd |
13 or 113 |
Europe default + milliseconds | dd mon yyyy hh:mi:ss:mmm(24h) |
14 or 114 |
- | hh:mi:ss:mmm(24h) |
20 or 120 |
ODBC canonical | yyyy-mm-dd hh:mi:ss(24h) |
21 or 121 |
ODBC canonical (with milliseconds) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
126 |
ISO8601 | yyyy-mm-ddThh:mi:ss.mmm (no spaces) |
127 |
ISO8601 with time zone Z. | yyyy-mm-ddThh:mi:ss.mmmZ (no spaces) |
130 |
Hijri | dd mon yyyy hh:mi:ss:mmmAM |
131 |
Hijri | dd/mm/yyyy hh:mi:ss:mmmAM |
SELECT CONVERT(VARCHAR, GETDATE(), 0) AS 'mon dd yyyy hh:mi(AM/PM)',
CONVERT(VARCHAR, GETDATE(), 101) AS 'mm/dd/yyyy',
CONVERT(VARCHAR, GETDATE(), 3) AS 'dd/mm/yy',
CONVERT(VARCHAR, GETDATE(), 104) AS 'dd.mm.yyyy',
CONVERT(VARCHAR, GETDATE(), 8) AS 'hh:mi:ss',
CONVERT(VARCHAR, GETDATE(), 20) AS 'yyyy-mm-dd hh:mi:ss(24h)'
| mon dd yyyy hh:mi(AM/PM) | mm/dd/yyyy | dd/mm/yy | dd.mm.yyyy | hh:mi:ss | yyyy-mm-dd hh:mi:ss(24h) |
|---|---|---|---|---|---|
| Dec 21 2023 04:23PM | 12/21/2023 | 21/12/23 | 21.12.2023 | 04:23:58 | 2023-23-21 16:23:58 |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
SELECT FirstName, LastName,
CONVERT(varchar, OrderDate, 104) AS Date,
TotalAmount
FROM [Order] O
JOIN Customer C ON C.Id = O.CustomerId
| FirstName | LastName | Date | TotalAmount |
|---|---|---|---|
| Paul | Henriot | 04.07.2012 | 440.00 |
| Karin | Josephs | 05.07.2012 | 1863.40 |
| Mario | Pontes | 08.07.2012 | 1813.00 |
| Mary | Saveley | 08.07.2012 | 670.80 |
| Pascale | Cartrain | 09.07.2012 | 3730.00 |
 |
|||
