Inserting date from string: CAST vs CONVERT

Question

Consider the following two ways to convert a datetime varchar string into a date field:

SELECT convert(date, '2012-12-21 21:12:00', 20) -- Only date is needed
SELECT cast('2012-12-21 21:12:00' as date) -- Only date is needed

Both return what I expect: The date excluding the time, in as a date datatype.

My question is: Is there any pro and cons of doing either way?

Answer 1

The (formerly) accepted answer iswas incorrect as it iswas a bad and misleading test. The two queries being compared do not do the same thing due to a simple typo that causes them to not be an apples-to-apples comparison. The test in the accepted answer is unfairly biased in favor of the CAST operation. The issue is that the CONVERT operation is being done with convert(date, GETDATE()+num,20) -- a value to convert that changes per row -- while the CAST operation is being done with a simple cast(GETDATE() as date) -- a value to convert that is consistent across all rows and is replaced in the execution plan as a constant. And in fact, looking at the XML execution plan even shows the actual operation performed as being CONVERT(date,getdate(),0) !!

Insofar as my testing shows (after making them equal via using cast(GETDATE()+num as date)), the times varry with them being mostly the same (which makes sense if they are both reduced to being CONVERT anyway) or the CONVERT winning:

SET STATISTICS IO, TIME ON;
    ;with t as (
               select convert(date, GETDATE(),20) as fecha , 0 as num
             union all
             select convert(date, GETDATE()+num,20) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

-- 4754-07-23
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 9031 ms,  elapsed time = 9377 ms.



-- VS    

SET STATISTICS IO, TIME ON;
    ;with t as (
               select cast(GETDATE() as date) as fecha , 0 as num
             union all
             select cast(GETDATE() as date) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

--2016-08-26
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 8969 ms,  elapsed time = 9302 ms.




SET STATISTICS IO, TIME ON;
    ;with t as (
               select cast(GETDATE() as date) as fecha , 0 as num
             union all
             select cast(GETDATE()+num as date) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);
SET STATISTICS IO, TIME OFF;

-- 4754-07-23
--Table 'Worktable'. Scan count 2, logical reads 6000008, physical reads 0, read-ahead reads 0

-- SQL Server Execution Times:
--   CPU time = 9438 ms,  elapsed time = 9878 ms.

The main difference between CAST and CONVERT is that CONVERT allows for the "style" to be specified. The "style" not only allows for tailoring the output when converting a non-string to a string, but also allows for specifying the input format when converting a string to a non-string:

SELECT CONVERT(DATE, '5/10/2016', 101); -- 101 = mm/dd/yyyy
-- 2016-05-10


SELECT CONVERT(DATE, '5/10/2016', 103); -- 103 = dd/mm/yyyy
-- 2016-10-05

Now compare that functionally with CAST:

SELECT CAST('13/5/2016' AS DATE);
-- Msg 241, Level 16, State 1, Line 71
-- Conversion failed when converting date and/or time from character string.


SELECT CONVERT(DATE, '13/5/2016', 101); -- 101 = mm/dd/yyyy
-- Msg 241, Level 16, State 1, Line 76
-- Conversion failed when converting date and/or time from character string.


SELECT CONVERT(DATE, '13/5/2016', 103); -- 103 = dd/mm/yyyy
-- 2016-05-13

---

One additional thing to mention about CAST: because it does not have the "style" parameter, the format of the date string passed in is assumed to be that of the current culture (a session property). The current culture is denoted by the @@LANGID and @@LANGUAGE system variables. This means that the CAST statement that failed in the test directly above could succeed for a different culture / language. The following tests shows this behavior and how that same date string does work with CAST when the current language is "French" (and would work with several others, based on the values in the dateformat column in sys.syslanguages):

IF (@@LANGID <> 0) -- us_english
BEGIN
    PRINT 'Changing LANGUAGE to English...';
    SET LANGUAGE ENGLISH;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;

SELECT @@LANGUAGE, CAST('13/5/2016' AS DATE) AS [Test 1];
-- Msg 241, Level 16, State 1, Line 71
-- Conversion failed when converting date and/or time from character string.
GO

SELECT @@LANGUAGE, CONVERT(DATE, '13/5/2016', 103) AS [Test 2]; -- 103 = dd/mm/yyyy
-- us_english   2016-05-13
GO


IF (@@LANGID <> 2) -- Français
BEGIN
    PRINT 'Changing LANGUAGE to French...';
    SET LANGUAGE FRENCH;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;

SELECT @@LANGUAGE, CAST('13/5/2016' AS DATE) AS [Test 3];
-- 2016-05-13
GO

SELECT @@LANGUAGE, CONVERT(DATE, '13/5/2016', 103) AS [Test 4]; -- 103 = dd/mm/yyyy
-- Français 2016-05-13
GO


-- Reset current language, if necessary.
IF (@@LANGID <> @@DEFAULT_LANGID)
BEGIN
    DECLARE @Language sysname;

    SELECT @Language = sl.[alias]
    FROM   sys.syslanguages sl
    WHERE  sl.[langid] = @@DEFAULT_LANGID;

    PRINT N'Changing LANGUAGE back to default: ' + @Language + N'...';

    SET LANGUAGE @Language;
    SELECT @@LANGUAGE AS [CurrentLanguage], @@LANGID AS [LangID];
END;

Answer 2

I'm not aware of any 'performance' differences between the two. Apparently "CONVERT" is Sql Server specific while CAST is ANSI-standard. I believe CONVERT gives you more options. You can see other pros/cons here (http://searchsqlserver.techtarget.com/tip/The-difference-between-CONVERT-and-CAST-in-SQL-Server)

Direct quotes from link...

Because SQL Server provides both functions , there may be some confusion about which is best to use and under what circumstances.

CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers.

CAST is the more ANSI-standard of the two functions, meaning that while it's more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it's also less powerful.

Answer 3

I tried this silly comparison. Edited, there was a typo that @srutzky brought up. The results are close.

    ;with t as (
               select convert(date, GETDATE(),20) as fecha , 0 as num
             union all
             select convert(date, GETDATE()+num,20) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);

-- VS    
    ;with t as (
               select cast(GETDATE() as date) as fecha , 0 as num
             union all
             select cast(GETDATE()+num as date) as fecha, num+1 from t where num<1000000)
    select max(fecha)
      from t
    option (maxrecursion  0);

Results where very consistent:

• Convert: 8.6 seconds
• Cast: 8.7 seconds