Changing the date format

The style parameter of convert provides a variety of date display formats for converting datetime or smalldatetime data to char or varchar. The number argument you supply as the style parameter determines how the data appears. The year can use either two or four digits. Add 100 to a style value to get a 4-digit year, including the century (yyyy)

Table 16-5 shows the possible values for style, and the variety of date formats you can use. When you use style with smalldatetime, the styles that include seconds or milliseconds show zeros in those positions.

Key to table “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.

Table 16-5: Converting date formats with the style parameter.

Without century (yy)

With century (yyyy)

Standard

Output

-

0 or 100

Default

mon dd yyyy hh:mm AM (or PM)

1

101

USA

mm/dd/yy

2

2

SQL standard

yy.mm.dd

3

103

English/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

dd-mm-yy

6

106

dd mon yy

7

107

mon dd, yy

8

108

HH:mm:ss

-

9 or 109

Default + milliseconds

mon dd yyyy hh:mm:sss AM (or PM)

10

110

USA

mm-dd-yy

11

111

Japan

yy/mm/dd

12

112

ISO

yymmdd

13

113

yy/dd/mm

14

114

mm/yy/dd

15

115

dd/yy/mm

-

16 or 116

mon dd yyyy HH:mm:ss

17

117

hh:mmAM

18

118

HH:mm

19

hh:mm:ss:zzzAM

20

HH:mm:ss:zzz

21

yy/mm/dd HH:mm:ss

22

yy/mm/dd hh:mm AM (or PM)

23

yyyy-mm-ddTHH:mm:ss

The default values, style 0 or 100, and 9 or 109, always return the century (yyyy).

This example converts the current date to style 3, dd/mm/yy:

select convert(char(12), getdate(), 3)

When converting date data to a character type, use style numbers 1 – 7 (101 – 107) or 10 – 12 (110 – 112) in Table 16-5 to specify the display format. The default value is 100 (mon dd yyyy hh:miAM (or PM )). If date data is converted to a style that contains a time portion, that time portion reflects the default value of zero.When converting time data to a character type, use style number 8 or 9 (108 or 109) to specify the display format. The default is 100 (mon dd yyyy hh:miAM (or PM)). If time data is converted to a style that contains a date portion, the default date of Jan 1, 1900 appears.

Noteconvert with NULL in the style argument returns the same result as convert with no style argument. For example:

select convert(datetime, "01/01/01")
------------
Jan 1 2001 12:00AM
select convert(datetime, "01/01/01", NULL)
------------
Jan 1 2001 12:00AM