datetime Cast or Convert?

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.

@Learner I asked about datetime casts / converts, not generally. Because'Convert' is more flexible to convert to format dates. — Fabian Bigler, Jul 02 '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. — Taryn, Jul 02 '13 at 19:19
@bluefeet I don't ask for subjective answers though. I ask for pragmatic thinking. — Fabian Bigler, Jul 02 '13 at 19:20
The way you worded the question is subjective though _When would you choose Cast over Convert_. — Taryn, Jul 02 '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 02 '13 at 19:30
@AndriyM I didn't conclude that for both things apply the same rules. I could imagine there was some other differences why to choose one or the other. Now my doubts have disappeared - thank you. — Fabian Bigler, Jul 02 '13 at 20:11

Roman Pekar · Accepted Answer · 2013-07-02T19:15:53.837

9

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

edited Jul 02 '13 at 19:15

answered Jul 02 '13 at 19:00

Roman Pekar

107,110
28
195
197

+1 for nice example 'cast('20130302' as date)' – Fabian Bigler Jul 02 '13 at 19:12
6

If you cast `cast('20130302' as date)`, I expect you get 2013-03-02, so 2013 March, 02. Always. – ypercubeᵀᴹ Jul 02 '13 at 19:15
7

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 02 '13 at 19:15
1

@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 02 '13 at 19:16
2

Yes, or with something like `07/05/12`. – ypercubeᵀᴹ Jul 02 '13 at 19:46
@RomanPekar that's quite easy, you will always get March 2. YYYYDDMM doesn't exist as a standard, YYYYMMDD does! – kiradotee Oct 01 '18 at 12:43

Learner · Answer 2 · 2013-07-02T19:06:49.637

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/

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 02 '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 02 '13 at 19:25

datetime Cast or Convert?

2 Answers2

Update

Linked