datetime Cast or Convert? [duplicate]

Question

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.

Answer 1

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

Answer 2

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.

Update

Check this: http://blog.sqlauthority.com/2012/11/21/sql-server-display-datetime-in-specific-format-sql-in-sixty-seconds-033-video/