How to Insert date from a string: CAST vs CONVERT?

The (formerly) accepted answer was incorrect as it was 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 xss=removed xss=removed xss=removed xss=removed xss=removed xss=removed></pre><pre><code>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;