CONVERT()

CONVERT()

CONVERT() is normally used to convert data to a new data type. It can also be used to display date & time in different formats.

Syntax:
CONVERT ( data_type [ ( length ) ] ,expression [ ,style ] )

Below table shows the style values you can use. The examples below will make this more clear.

Without century (yy)	With century (yyyy)	Standard	Input/Output**
-	0 or 100 (*)	Default	mon dd yyyy hh:miAM (or PM)
1	101	USA	mm/dd/yy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yy
4	104	German	dd.mm.yy
5	105	Italian	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:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd
-	13 or 113 (*)	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	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-dd Thh:mm:ss.mmm(no spaces)
-	130*	Hijri****	dd mon yyyy hh:mi:ss:mmmAM
-	131*	Hijri****	dd/mm/yy hh:mi:ss:mmmAM

[Source of above table]

Example 1 of CONVERT()

Select CONVERT(VARCHAR(20),GETDATE())

Result:
Jul 8 2010 7:28PM

Example 2 of CONVERT()

Using style 107

Select CONVERT(VARCHAR(24),GETDATE(),107)

Result:
Jul 08, 2010

Example 3 of CONVERT()

Using style 100

select CONVERT(VARCHAR(24),GETDATE(),100)

Result:
Jul 8 2010 7:30PM

Example 4 of CONVERT()

As another example consider Table_A below which has Dep_date column of datetime datatype. Notice that the column Dep_Date has time also.

select * from Table_A

What if we want all records for date '2010-08-01', no matter what time it is? If we use the below query, it will not result in anything.

select * from Table_A where dep_Date = '2010-08-01'

No Results

Below we are using Convert() function to get the results:

Select * from Table_A
Where Convert(varchar,Dep_date,101)='08/01/2010'