Inserting date from string: CAST vs CONVERT

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?

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

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;
`

StackExchange Database Administrators Q#147976, answer score: 11