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;