Take the 2-minute tour ×

This question already has an answer here:

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:


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

share|improve this question
@Learner I asked about datetime casts / converts, not generally. Because'Convert' is more flexible to convert to format dates. –  Fabian Bigler Jul 2 '13 at 18:55
@FabianBigler Part of the problem is this is opinion based, when I use cast or convert might be different from when someone else uses it. –  bluefeet Jul 2 '13 at 19:19
@bluefeet I don't ask for subjective answers though. I ask for pragmatic thinking. –  Fabian Bigler Jul 2 '13 at 19:20
The way you worded the question is subjective though When would you choose Cast over Convert. –  bluefeet Jul 2 '13 at 19:21
What is the point of asking this specifically in connection with datetime? The accepted answer to the question linked covers everything that you need to know: CAST is ANSI, CONVERT more flexible. When ANSI compliance is the priority, use CAST. When you need flexibility, use CONVERT. –  Andriy M Jul 2 '13 at 19:30
show 2 more comments

marked as duplicate by Martin Smith, bluefeet, Roman Pekar, swasheck, Andriy M Jul 2 '13 at 19:30


2 Answers

up vote 3 down vote accepted

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

share|improve this answer
+1 for nice example 'cast('20130302' as date)' –  Fabian Bigler Jul 2 '13 at 19:12
If you cast cast('20130302' as date), I expect you get 2013-03-02, so 2013 March, 02. Always. –  ypercube Jul 2 '13 at 19:15
Actually, you chose a bad example to make your case. The YYYYMMDD format would always be interpreted unambiguously in SQL Server, and so '20130302' would convert as March 2, 2013 without a format specifier. –  Andriy M Jul 2 '13 at 19:15
@AndriyM Yes, you right, I've just wanted to point it out that YYYYMMDD is ISO format in SQL Server, but you was faster. Anyway, I think that my point about cast and convert for date/datetime is clear enough. I'll try to use yyyy-mm-dd if I have to give an example in the future :) –  Roman Pekar Jul 2 '13 at 19:16
Yes, or with something like 07/05/12. –  ypercube Jul 2 '13 at 19:46
add comment

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.

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

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.


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

share|improve this answer
Thank you. I have already seen the difference between cast and convert as you mentioned, in the MSDN Specifications. But that is not answering my question when to use which one. Is there no rule of thumb in which cases to use cast or convert? –  Fabian Bigler Jul 2 '13 at 19:10
From my understanding, there is no rule of thumb. I prefer to use Convert as it makes date formatting super easy. –  Learner Jul 2 '13 at 19:25
add comment
