從字元串中插入日期：CAST vs CONVERT

考慮以下兩種將 datetime varchar 字元串轉換為日期欄位的方法：

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

兩者都返回我所期望的：不包括時間的日期，作為日期數據類型。

我的問題是：做這兩種方式有什麼利弊嗎？

（以前）接受的答案是不正確，因為它是是一個糟糕且具有誤導性的測試。由於一個簡單的錯字導致它們不是蘋果對蘋果的比較，被比較的兩個查詢不會做同樣的事情。接受答案中的測試不公平地偏向於CAST操作。問題是CONVERT操作正在完成convert(date, GETDATE()+num,20)- 一個轉換每行更改的值 - 而CAST操作正在完成一個簡單的cast(GETDATE() as date)- 一個要轉換的值，在所有行中都是一致的，並在執行計劃中被替換作為常數。事實上，查看 XML 執行計劃甚至顯示實際執行的操作是CONVERT(date,getdate(),0)!!

就我的測試顯示（在通過 using 使它們相等之後）而言，隨著時間的變化，它們幾乎相同（如果它們都被簡化為無論如何cast(GETDATE()+num as date)都是有道理的）或獲勝：CONVERT``CONVERT

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.

CAST 和 CONVERT之間的主要區別在於允許CONVERT指定“樣式”。“樣式”不僅允許在將非字元串轉換為字元串時定制輸出，還允許在將字元串轉換為非字元串時指定輸入格式：

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

現在將其與功能進行比較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

---

另外要提一件事CAST：因為它沒有“樣式”參數，所以傳入的日期字元串的格式被假定為目前文化的格式（會話屬性）。目前區域性由@@LANGID和@@LANGUAGE系統變數表示。這意味著CAST直接在上面測試中失敗的語句對於不同的文化/語言可能會成功。以下測試顯示了這種行為以及CAST噹噹前語言為“法語”時相同的日期字元串如何工作（並且可以與其他幾種語言一起工作，基於 中dateformat列中的值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;

引用自：https://dba.stackexchange.com/questions/147976