datetime Cast or Convert? [duplicate]

What to choose: Cast or Convert for datetimes (Microsoft SQL Server)?

I have had a look at the MSDN Specifications. At the first glance it seems there is no difference, except for the syntax:

Syntax for CAST:

CAST ( expression AS data_type [ ( length ) ] )

Syntax for CONVERT:

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

And CAST is ANSI-standard, which makes it more portable accross different database platforms.

2 Answers

convert has an optional parameter style, and I suggest to use convert instead of cast. It helps to avoid confusion. For example, if you write cast('20130302' as date), what would you get? March 2 or February 3?

Also, if you want specific format when casting to date to string, you bound to use convert

The MSDN Specification link that you have posted, If you read carefully you will find your answer under section G:

G. Using CAST and CONVERT with datetime data

The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.

SELECT GETDATE() AS UnconvertedDateTime, CAST(GETDATE() AS nvarchar(30)) AS UsingCast, CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO

Here is the result set.

UnconvertedDateTime UsingCast UsingConvertTo_ISO8601
----------------------- ------------------------------ ------------------------------
2006-04-18 09:58:04.570 Apr 18 2006 9:58AM 2006-04-18T09:58:04.570

(1 row(s) affected)

You can clearly see the difference.