SQL Server CONVERT()Function

SQL DatesSQL Server Date Functions

Definition and Usage

The CONVERT() function is a general function that converts a date to a new data type.

The CONVERT() function can display date/time data in different formats.

Syntax

CONVERT(data_type(length),expression,style)

Value Description
data_type(length) Specifies the target data type (with optional length).
expression Specifies the value to be converted.
style Specifies the output format for date/time.

The following table shows the style values when converting datetime or smalldatetime to character data:

Value
(Century yy)		 Value
(Century yyyy)		 Input/Output Standard
- 0 or 100 mon dd yyyy hh:miAM (or PM) Default
1 101 mm/dd/yy USA
2 102 yy.mm.dd ANSI
3 103 dd/mm/yy British/French
4 104 dd.mm.yy German
5 105 dd-mm-yy Italian
6 106 dd mon yy
7 107 Mon dd, yy
8 108 hh:mm:ss
- 9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default+millisec
10 110 mm-dd-yy USA
11 111 yy/mm/dd Japan
12 112 yymmdd ISO
- 13 or 113 dd mon yyyy hh:mi:ss:mmm (24h)
14 114 hh:mi:ss:mmm (24h)
- 20 or 120 yyyy-mm-dd hh:mi:ss (24h)
- 21 or 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
- 126 yyyy-mm-ddThh:mi:ss.mmm (no spaces) ISO8601
- 130 dd mon yyyy hh:mi:ss:mmmAM Hijiri
- 131 dd/mm/yy hh:mi:ss:mmmAM Hijiri

Examples

The following script uses the CONVERT() function to display different formats. We will use the GETDATE() function to get the current date/time:

Examples

SELECT CONVERT(INT, 25.65);
       
SELECT CONVERT(VARCHAR(19),GETDATE())

SELECT CONVERT(VARCHAR(10),GETDATE(),10)

SELECT CONVERT(VARCHAR(10),GETDATE(),110)

SELECT CONVERT(VARCHAR(11),GETDATE(),6)

SELECT CONVERT(VARCHAR(11),GETDATE(),106)

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

The above statements output respectively as:

25
Apr 15 2021 7:59AM
04-15-21
04-15-2021
15 Apr 21
15 Apr 2021
15 Apr 2021 08:03:37:767

SQL DatesSQL Server Date Functions Other Extensions