將bigint顯式轉換為日期-有解無憂

我正在修復現有代碼，但由于 SQL Server 一直顯示與將字串轉換為日期然后將 bigint 轉換為日期相關的錯誤，所以我被困住了。所以不確定使用哪種技術：

下面是我的代碼：

SELECT
c.WCFromDate
,c.WCToDate
,CONVERT(date, CAST(
                    CASE
                        WHEN c.WCFromDate = 0  THEN 19000101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) = '00' AND RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000   0101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) = '02' AND RIGHT(c.WCFromDate,2) > '29'
                            THEN LEFT(c.WCFromDate, 4) * 10000   0228
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) < '13' AND RIGHT(c.WCFromDate,2) > '31'
                            THEN LEFT(c.WCFromDate, 4) * 10000   0101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) > '12'
                            THEN LEFT(c.WCFromDate, 4) * 10000   0101
                        WHEN RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000   0101
                        ELSE c.WCFromDate
                    END AS varchar(10))
        ) AS [Worker Compensation From Date]
,CONVERT(date, CAST(
                    CASE
                        WHEN c.WCToDate = 0 THEN 19000101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) = '00' AND RIGHT(c.WCToDate,2) = '00'
                            THEN LEFT(c.WCToDate, 4) * 10000   0101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) = '02' AND RIGHT(c.WCToDate,2) > '29'
                            THEN LEFT(c.WCToDate, 4) * 10000   0228
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) < '13' AND RIGHT(c.WCToDate,2) > '31'
                            THEN LEFT(c.WCToDate, 4) * 10000   0101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) > '12'
                            THEN LEFT(c.WCToDate, 4) * 10000   0101
                        WHEN RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000   0101
                        ELSE c.WCToDate
                    END AS varchar(10))
        ) AS [Worker Compensation To Date]
        FROM  ls3dwh.Claims c

更具體地說，所有情況下陳述句作業正常但 ELSE 部分導致此錯誤。我使用 ELSE NULL 來了解更多資訊，并注意到所有 ELSE 值都導致了這個錯誤。

列值非常簡單，下面的螢屏截圖可以提供幫助：

所有 4 列

這是前兩列的一些示例資料。我使用了一個不同的選擇：

   WCFromDate   WCToDate
   0        0
   19456601 20030000
   19580800 20300500
   19670000 0
   19840529 19840603
   19850314 19851115
   19851121 19910110
   19860217 19861112

我得到的錯誤是這樣的：

訊息 241，級別 16，狀態 1，第 1 行
從字串轉換日期和/或時間時轉換失敗。

uj5u.com熱心網友回復：

您的問題源于您回傳了不同的資料型別，因此編譯器嘗試將它們全部強制為更高優先級的型別，在這種情況下是date.

相反，您可以隨時回傳DATEFROMPARTS，或者在必要時進行轉換。

假設您的資料型別確實如此，varchar那么您可以清理代碼并簡化各種條件，如下所示：

SELECT
  c.WCFromDate
 ,c.WCToDate
 ,CASE
    WHEN c.WCFromDate = '0'
      THEN '19000101'
    WHEN SUBSTRING(c.WCFromDate, 5, 2) = '02' AND RIGHT(c.WCFromDate, 2) > '29'
      THEN DATEFROMPARTS(LEFT(c.WCFromDate, 4), 2, 28)
    WHEN SUBSTRING(CAST(c.WCFromDate, 5, 2) NOT BETWEEN '01' AND '13'
         OR RIGHT(c.WCFromDate, 2) NOT BETWEEN '01' AND '31'
      THEN DATEFROMPARTS(LEFT(c.WCFromDate, 4), 1, 1)
    ELSE
      TRY_CONVERT(date, c.WCFromDate, 112)
    END AS [Worker Compensation From Date]
 ,CASE
    WHEN c.WCToDate= '0'
      THEN '19000101'
    WHEN SUBSTRING(c.WCToDate, 5, 2) = '02' AND RIGHT(c.WCToDate, 2) > '29'
      THEN DATEFROMPARTS(LEFT(c.WCToDate, 4), 2, 28)
    WHEN SUBSTRING(CAST(c.WCToDate, 5, 2) NOT BETWEEN '01' AND '13'
         OR RIGHT(c.WCToDate, 2) NOT BETWEEN '01' AND '31'
      THEN DATEFROMPARTS(LEFT(c.WCToDate, 4), 1, 1)
    ELSE
      TRY_CONVERT(date, c.WCToDate, 112)
    END AS [Worker Compensation To Date]
FROM  ls3dwh.Claims c

如果您的資料型別實際上是bigint，那么我建議您使用%和/除法來獲取不同的部分，而不是SUBSTRING.

uj5u.com熱心網友回復：

您是否考慮過使用 IsDate(MyFieldValue) 來檢查有效日期，而不是決議多個案例陳述句來檢查日期有效性？這可能會使該程序更清晰，更易于理解和除錯，并且可以幫助您獲得所需的輸出。我只是快速檢查了一下，它“按原樣”作業，值的格式與您共享的值類似。

uj5u.com熱心網友回復：

試試下面的 case 運算式：

Select case 
        when len(d)=8 and ((d%10000)=0 or (d%10000) >1231 or (d%100)>31 
        or (d%100)=0  or (d%10000/100)=0
        or ((d%10000/100) in(4,6,9,11) and d%100>30))
        then cast(cast(d 101-(d%10000) as varchar(10)) as date)
        
        when len(d)=8 and ((d%10000) between 230 and 299)
        then cast(cast(d 228-(d%10000) as varchar(10)) as date)
        
        when d=0 then cast('19000101' as date)
        else cast(d as date)
      end as dates
from dts

該運算式將檢查日期值中所有可能的錯誤（如您列出的）并根據您的條件進行修復。

從這里查看演示。

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/494356.html

標籤：sql sql服务器日期大整数

上一篇：如何限制正則運算式中的小數點？

下一篇：在R中使用合并填充缺失的日期