CAST and CONVERT (Transact-SQL)

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

    

        

            
Without century (yy) (1) 
With century (yyyy) 
Standard 
Input/Output (3) 
        
        

            

            
-
            
            

            
0 or 100 (1, 2) 
            
            

            
Default
            
            

            
mon dd yyyy hh:miAM (or PM)
            
        
        

            

            
1 
            
            

            
101 
            
            

            
U.S.
            
            

            
mm/dd/yyyy
            
        
        

            

            
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 (1)
            
            

            
-
            
            

            
dd mon yy
            
        
        

            

            
7 
            
            

            
107 (1)
            
            

            
-
            
            

            
Mon dd, yy
            
        
        

            

            
8 
            
            

            
108 
            
            

            
-
            
            

            
hh:mi:ss
            
        
        

            

            
-
            
            

            
9 or 109 (1, 2) 
            
            

            
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 (1, 2) 
            
            

            
Europe default + milliseconds
            
            

            
dd mon yyyy hh:mi:ss:mmm(24h)
            
        
        

            

            
14 
            
            

            
114 
            
            

            
-
            
            

            
hh:mi:ss:mmm(24h)
            
        
        

            

            
-
            
            

            
20 or 120 (2) 
            
            

            
ODBC canonical
            
            

            
yyyy-mm-dd hh:mi:ss(24h)
            
        
        

            

            
-
            
            

            
21 or 121 (2) 
            
            

            
ODBC canonical (with milliseconds)
            
            

            
yyyy-mm-dd hh:mi:ss.mmm(24h)
            
        
        

            

            
-
            
            

            
126 (4)
            
            

            
ISO8601
            
            

            
yyyy-mm-ddThh:mi:ss.mmm (no spaces)
            
        
        

            

            

            
127(6, 7)
            
            

            
ISO8601 with time zone Z.
            
            

            
yyyy-mm-ddThh:mi:ss.mmmZ 
            
(no spaces)
            
        
        

            

            
-
            
            

            
130 (1, 2)
            
            

            
Hijri (5)
            
            

            
dd mon yyyy hh:mi:ss:mmmAM
            
        
        

            

            
-
            
            

            
131 (2)
            
            

            
Hijri (5)
            
            

            
dd/mm/yy hh:mi:ss:mmmAM
            
        
    

http://msdn2.microsoft.com/en-us/library/ms187928.aspx