SQL Convert DATETIME to String

Converting a datetime to a string value is a common requirement in database solutions.

Use CONVERT or TRY_CONVERT to perform this operation.

Example

#

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.

Result:  1 record
String
Dec 7 2021 03:24PM

Converting DATETIME to String

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

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

More Examples

Convert DATETIME to String with different formats

Convert the current datetime to string values in different formats.
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)'
Result:  1 record
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 7 2021 03:24PM 12/07/2021 07/12/21 07.12.2021 03:24:41 2021-24-07 15:24:41

Convert DATETIME to String with COLUMN value

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List orders with order dates in German date format.
SELECT FirstName, LastName,
       CONVERT(varchar, OrderDate, 104) AS Date,
       TotalAmount
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
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

You may also like



Guides


vsn 3.1