依斜線拆解日期欄位

--依斜線拆解日期欄位
SELECT REDATE,* FROM
(
  SELECT SUBSTRING(REDATE,1,POS1-1) AS YYYY
  ,SUBSTRING(REDATE,POS1+1,POS2-1) AS MM
  ,SUBSTRING(REDATE,POS1+POS2+1,3) AS DD
  ,*
  FROM
  (
    --取出第一個斜線位置
    SELECT CHARINDEX('/',REDATE)  AS POS1
    --取出第二個斜線位置
    ,CHARINDEX('/',SUBSTRING(REDATE,CHARINDEX('/',REDATE)+1,10)) AS POS2
    ,* FROM FDRDMDMI_TMP
  ) TA
  WHERE ISNULL(KIND,'')<>'Y' AND POS1 >0
) TB

--檢查日期正確性
SELECT CAST (REDATE AS DATETIME),
REDATE,COSNO,VIPNO,NAME
FROM FDRDMDMI_TMP
WHERE ISNULL(KIND,'')<>'Y'

--找出錯誤日期格式
DECLARE @iCNT INT
SET @ICNT=8170
EXEC('SELECT TOP '+@ICNT+' REDATE,*FROM FDRDMDMI_TMP  WHERE ISNULL(KIND,'''')<>''Y''')

 

--年份有錯之資料作上註記
UPDATE FDRDMDMI_TMP SET KIND='Y'
WHERE REDATE IN(
  SELECT REDATE FROM
  (
    SELECT SUBSTRING(REDATE,1,POS1-1) AS YYYY
    ,SUBSTRING(REDATE,POS1+1,POS2-1) AS MM
    ,SUBSTRING(REDATE,POS1+POS2+1,3) AS DD
    ,*
    FROM
    (
      --取出第一個斜線位置
      SELECT CHARINDEX('/',REDATE)  AS POS1
      --取出第二個斜線位置
      ,CHARINDEX('/',SUBSTRING(REDATE,CHARINDEX('/',REDATE)+1,10)) AS POS2
      ,* FROM FDRDMDMI_TMP
    ) TA
    WHERE ISNULL(KIND,'')<>'Y' AND POS1 >0
  ) TB
WHERE LEN(YYYY)>4
)