T-SQL Cast versus Convert

Question

What is the general guidance on when you should use CAST versus CONVERT? Is there any performance issues related to choosing one versus the other? Is one closer to ANSI-SQL?

Matthew Farwell · Accepted Answer · 2014-03-21T12:23:07.717

389

CONVERT is SQL Server specific, CAST is ANSI.

CONVERT is more flexible in that you can format dates etc. Other than that, they are pretty much the same. If you don't care about the extended features, use CAST.

EDIT:

As noted by @beruic and @C-F in the comments below, there is possible loss of precision when an implicit conversion is used (that is one where you use neither CAST nor CONVERT). For further information, see CAST and CONVERT and in particular this graphic: SQL Server Data Type Conversion Chart. With this extra information, the original advice still remains the same. Use CAST where possible.

edited Mar 21 '14 at 12:23

answered Apr 01 '09 at 21:39

Matthew Farwell

60,889
18
128
171

5

Also, I believe there are some numerical conversions where CAST should be used to preserve precision, but I'm having trouble finding a reliable source for this information. – beruic May 23 '13 at 12:14
2

@beruic You're right, there is info in MSDN: http://msdn.microsoft.com/en-us/library/ms187928.aspx CAST is required to preserve precision when converting between DECIMAL and NUMERIC types. – C-F Mar 19 '14 at 02:05
@C-F Where do you see this information? I have followed the link, which opens thhe general page on CAST and CONVERT, and the only information regarding precision I can find is about conversion of float values that use scientific notation. Might I be wrong in my initial comment? – beruic Mar 19 '14 at 13:22
6

@beruic It's about this picture at the bottom of the article http://i.msdn.microsoft.com/dynimg/IC170617.gif Now I think that maybe the precision loss can happen in implicit conversion and doesn't happen when either CAST or CONVERT is used. It's not quite clear... – C-F Mar 20 '14 at 21:06
2

@C-F I agree that it is not very clear, and there should definitely be more specific documentation about it, so lets hope Microsoft does this. But well spotted there :) – beruic Mar 21 '14 at 09:12
@C-F I've added the extra information to the answer. Thanks – Matthew Farwell Mar 21 '14 at 12:23
Bear in mind that CONVERT is a 100% Microsoft proprietary SQL extension, thus you will have a harder time reusing your queries and procedures in 99% of the relational databases. The standard function for converting datatypes in SQL is CAST. – alejandrob Oct 07 '20 at 16:11

score 31 · Answer 2 · answered Apr 01 '09 at 21:22

31

Convert has a style parameter for date to string conversions.

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

answered Apr 01 '09 at 21:22

jason saldo

9,804
5
34
41

Elaskanator · Answer 3 · 2018-06-13T16:29:27.163

19

To expand on the above answer_{copied by Shakti}, I have actually been able to measure a performance difference between the two functions.

I was testing performance of variations of the solution to this question and found that the standard deviation and maximum runtimes were larger when using CAST.

_{*Times in milliseconds, rounded to nearest 1/300th of a second as per the precision of the DateTime type}

edited Jun 13 '18 at 16:29

answered Jun 13 '18 at 16:19

Elaskanator

1,135
10
28

score 16 · Answer 4 · edited Oct 31 '19 at 15:37

CAST is standard SQL, but CONVERT is only for the dialect T-SQL. We have a small advantage for convert in the case of datetime.

With CAST, you indicate the expression and the target type; with CONVERT, there’s a third argument representing the style for the conversion, which is supported for some conversions, like between character strings and date and time values. For example, CONVERT(DATE, '1/2/2012', 101) converts the literal character string to DATE using style 101 representing the United States standard.

score 11 · Answer 5 · answered Feb 22 '18 at 23:04

11

Something no one seems to have noted yet is readability. Having…

CONVERT(SomeType,
    SomeReallyLongExpression
    + ThatMayEvenSpan
    + MultipleLines
    )

…may be easier to understand than…

CAST(SomeReallyLongExpression
    + ThatMayEvenSpan
    + MultipleLines
    AS SomeType
    )

answered Feb 22 '18 at 23:04

Atario

1,371
13
24

9

But, I think the CAST is usually more readable. `CAST(Column1 AS int)` is more logical to read than `CONVERT(int, Column1)` even for long expressions – S.Serpooshan Dec 30 '18 at 06:25
Given that `CAST` is the standard, I see `CAST` far more often so that's become the 'more readable' version to me – Bilbottom Oct 07 '22 at 07:34

score 6 · Answer 6 · answered Dec 01 '16 at 04:26

6

CAST uses ANSI standard. In case of portability, this will work on other platforms. CONVERT is specific to sql server. But is very strong function. You can specify different styles for dates

answered Dec 01 '16 at 04:26

RakeshP

141
5
15

score 3 · Answer 7 · answered Sep 08 '20 at 15:57

You should also not use CAST for getting the text of a hash algorithm. CAST(HASHBYTES('...') AS VARCHAR(32)) is not the same as CONVERT(VARCHAR(32), HASHBYTES('...'), 2). Without the last parameter, the result would be the same, but not a readable text. As far as I know, You cannot specify that last parameter in CAST.

T-SQL Cast versus Convert

7 Answers7

Linked

Related