Below is a list of date & time styles supported by the CONVERT() function in SQL Server. These are the styles that can be used as the third argument to indicate how the input value is formatted.

Without century (yy)	With century (yyyy)	Standard	Input/output
–	0 or 100	Default for datetime and smalldatetime	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd
3	103	British/French	3 = dd/mm/yy 103 = dd/mm/yyyy
4	104	German	4 = dd.mm.yy 104 = dd.mm.yyyy
5	105	Italian	5 = dd-mm-yy 105 = dd-mm-yyyy
6	106	–	6 = dd mon yy 106 = dd mon yyyy
7	107	–	7 = Mon dd, yy 107 = Mon dd, yyyy
8 or 24	108	–	hh:mi:ss
–	9 or 109	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	10 = mm-dd-yy 110 = mm-dd-yyyy
11	111	JAPAN	11 = yy/mm/dd 111 = yyyy/mm/dd
12	112	ISO	12 = yymmdd 112 = yyyymmdd
–	13 or 113	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm (24-hour)
14	114	–	hh:mi:ss:mmm (24-hour)
–	20 or 120	ODBC canonical	yyyy-mm-dd hh:mi:ss (24-hour)
–	21 or 25 or 121	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset	yyyy-mm-dd hh:mi:ss.mmm (24-hour)
22	–	U.S.	mm/dd/yy hh:mi:ss AM (or PM)
–	23	ISO8601	yyyy-mm-dd
–	126	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
–	127	ISO8601 with time zone Z	yyyy-MM-ddThh:mm:ss.fffZ (no spaces)
–	130	Hijri	dd mon yyyy hh:mi:ss:mmmAM
–	131	Hijri	dd/mm/yyyy hh:mi:ss:mmmAM

The above table is intended to be a quick reference for the date and time styles that can be used with CONVERT(). Not all styles can be used with all conversions, and there are various other factors to consider when choosing the style. See the Microsoft documentation for a full rundown on these.

Example of Usage

Here’s a quick example of how to use the above styles:

SELECT CONVERT( date, '03/01/2035', 103 );

Result:

2035-01-03

In this case I used the style of 103. This indicates that the input value is formatted in that style. If we look at the above table, we can see that 103 indicates that it’s in British/French format. This means that the input value is provided as dd/mm/yyyy.

This is important because SQL Server could have mistaken it for another format. For example, here’s what happens on my system when I omit the style argument:

SELECT CONVERT( date, '03/01/2035' );

Result:

2035-03-01

It swapped the day and month parts around. This time SQL Server interpreted the date as being in mm/dd/yyyy format. This is because my language is set to us_english. The us_english format is mm/dd/yyyy. So, the style argument enables us to override this format when we do the conversion.

Just to be clear, here’s what happens when I change the language on my system, then run the conversion again without the style argument:

SET LANGUAGE British;
SELECT CONVERT( date, '03/01/2035' );

Result:

2035-01-03

This time it didn’t swap the day and month around, because my system was already in British/French format. Either way, we can use the date styles in the above table to change how the input value is formatted.