--依斜線拆解日期欄位
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
)
