How to Insert 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 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) !! To solve SQL server convert string to DateTime you can refer this answer as it has helped me in solving it .
Insofar as my testing shows (after making them equal via using cast(GETDATE()+num as date)), the times vary 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 numOne 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 show 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 date format column in sys.says languages):
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;