RSS Feed

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)StandardInput/Output**
-0 or 100 (*) Defaultmon dd yyyy hh:miAM (or PM)
1101USAmm/dd/yy
2102ANSIyy.mm.dd
3103British/Frenchdd/mm/yy
4104Germandd.mm.yy
5105Italiandd-mm-yy
6106-dd mon yy
7107-Mon dd, yy
8108-hh:mm:ss
-9 or 109 (*) Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USAmm-dd-yy
11111JAPANyy/mm/dd
12112ISOyymmdd
-13 or 113 (*) Europe default + millisecondsdd mon yyyy hh:mm:ss:mmm(24h)
14114-hh:mi:ss:mmm(24h)
-20 or 120 (*) ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)
-21 or 121 (*) ODBC canonical (with milliseconds)yyyy-mm-dd hh:mi:ss.mmm(24h)
-126(***)ISO8601yyyy-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'