Microsoft SQL Server：其中逻辑 - 存储为整数的列，但表示表中的日期

问：

我的 Where 子句遇到了问题;

AND CAST(h.chinto as DATETIME) >= DATEADD(DAY, -7, GetDate())

chinto 以 YYYYMMDD 格式存储为整数，表示日期。我正在尝试编写一个 where 子句来根据过去 30 天等对我的结果进行排序。

我试图将数据类型更改为日期，但我似乎无法正确处理。总是遇到错误：将表达式转换为数据类型 datetime 的算术溢出错误。 try_convert

SELECT top 100
    [ClaimNumber]   = MAX(RIGHT('00000000' + convert(varchar(8),h.chclno),8) + RIGHT('00' + convert(varchar(8),h.chwkno),2)),
    [EmployeeSSN]   = RTRIM(MAX(RIGHT('000000000' + CONVERT(VARCHAR(9),m.edssno),9))),
    [EmployeeID]    = RTRIM(MAX(ee.edmemb)),
    [EmployeeLastName] = RTRIM(MAX(m.edlnmk)),
    [EmployeeFirstName] = RTRIM(MAX(m.edfnam)), 
    [PatientSSN] = RTRIM(MAX(RIGHT('000000000' + CONVERT(VARCHAR(9),e.eessno),9))),
    [PatientDOB] = RTRIM(MAX(m.edbrdt)),
    [PatientGender] = RTRIM(MAX(m.edsexx)),
    -- m.edbrdt as 'PatientDOB',
    --m.edsexx as 'PatientGender',
    [FirstDateOfService] = MAX(left(convert(char(8),h.chinfr),4) + '-' + substring(convert(char(8),h.chinfr),5,2) + '-' + right(convert(char(8),h.chinfr),2) ),
    [LastDateOfService] = MAX(left(convert(char(8),h.chinto),4) + '-' + substring(convert(char(8),h.chinto),5,2) + '-' + right(convert(char(8),h.chinto),2) ),
    [ProviderName] = MAX(ps.pmname),
    [ProviderAddress1] = MAX(CASE WHEN sn.npinpi = nps.NPI THEN RTRIM(nps.Prov_First_Line_Business_Practice_Location_Address)               ELSE RTRIM(ps.pmadr1) COLLATE SQL_Latin1_General_Cp1_CS_AS END ),
    [ProviderAddress2] = MAX(CASE WHEN sn.npinpi = nps.NPI THEN RTRIM(ISNULL(nps.Prov_Second_Line_Business_Practice_Location_Address,''))   ELSE RTRIM(ps.pmadr2) COLLATE SQL_Latin1_General_Cp1_CS_AS END ),
    [ProviderCity] =   MAX(CASE WHEN sn.npinpi = nps.NPI THEN RTRIM(ISNULL(nps.Prov_Business_Mailing_Address_City_Name,'')) ELSE RTRIM(ps.pmadr2) COLLATE SQL_Latin1_General_Cp1_CS_AS END ),
    [ProviderState] = MAX(CASE WHEN sn.npinpi = nps.NPI THEN RTRIM(nps.Prov_Business_Practice_Location_Address_ST_Name)                 ELSE RTRIM(ps.pmstat) COLLATE SQL_Latin1_General_Cp1_CS_AS END ),
    [ProviderZIP] = MAX(CASE WHEN sn.npinpi = nps.NPI THEN LEFT(nps.Prov_Business_Practice_Location_Address_Postal_CD,5)                ELSE LEFT(ps.pmzpcd,5) COLLATE SQL_Latin1_General_Cp1_CS_AS END ),
    --[ProviderTIN] ,
    --[ProviderTINSuffix] ?,
    [DiagnosisCode1] = RTRIM(MAX(h.chdgcd)),
    [DiagnosisCode2] = RTRIM(MAX(ISNULL(h.chadgc_1,''))),
    [DiagnosisCode3] = RTRIM(MAX(ISNULL(h.chadgc_2,''))),
    [DiagnosisCode4] = RTRIM(MAX(ISNULL(h.chadgc_3,''))),
    [DiagnosisCode5] = RTRIM(MAX(ISNULL(h.chadgc_4,''))),
    [DiagnosisCode6] = RTRIM(MAX(ISNULL(h.chadgc_5,''))),
    [DiagnosisCode7] = RTRIM(MAX(ISNULL(h.chadgc_6,''))),
    [DiagnosisCode8] = RTRIM(MAX(ISNULL(h.chadgc_7,''))),
    [DiagnosisCode9] = RTRIM(MAX(ISNULL(h.chadgc_8,''))),
    --[ProcedureCode1?] = RTRIM(MAX(d.cdproc)) ---> clmdet missing prod code(s)/date(s) joined ACO.dbo.clmubf p 
    [ProcedureCode1] = RTRIM(MAX(ISNULL(p.cwprcd_1, ''))),
    [ProcedureDate1] = RTRIM(MAX(ISNULL(p.cwprdt_1, ''))),
    [ProcedureCode2] = RTRIM(MAX(ISNULL(p.cwprcd_2, ''))),
    [ProcedureDate2] = RTRIM(MAX(ISNULL(p.cwprdt_2, ''))),
    [ProcedureCode3] = RTRIM(MAX(ISNULL(p.cwprcd_3, ''))),
    [ProcedureDate3] = RTRIM(MAX(ISNULL(p.cwprdt_3, ''))),
    [ProcedureCode4] = RTRIM(MAX(ISNULL(p.cwprcd_4, ''))),
    [ProcedureDate4] = RTRIM(MAX(ISNULL(p.cwprdt_4, ''))),
    [ProcedureCode5] = RTRIM(MAX(ISNULL(p.cwprcd_5, ''))),
    [ProcedureDate5] = RTRIM(MAX(ISNULL(p.cwprdt_5, ''))),
    [ProcedureCode6] = RTRIM(MAX(ISNULL(p.cwprcd_6, ''))),
    [ProcedureDate6] = RTRIM(MAX(ISNULL(p.cwprdt_6, ''))),
    [TotalCharge]   = SUM(d.cdtchg),
    --[TotalCharge] = SUM(c.dichga?) --> drghis table missing from ACO.dbo, has all the info I need (clmdet d missing columns)
   --[AllowableCharge] = ?,
    [TotalIneligible] = SUM(d.cdinea),
    --[TotalEligible] =,
    [TotalDiscount] = SUM(d.cddisc),
    [TotalDeductible] = SUM(d.cddeda),
    [TotalCoPay] = SUM(d.cdcopa),
    [TotalCoInsurance] = SUM(d.cdleva_1)
    --[TotalPaid] = SUM(d.cdcoba?)
    --[ClaimType]

FROM 
    ACO.dbo.clmhdr h        
    INNER JOIN ACO.dbo.clmdet d ON h.chclno = d.cdclno AND h.chwkno = d.cdwkno
    INNER JOIN ACO.dbo.elgdep m ON h.chemno = m.ednmbr AND h.chdpcd = m.eddpcd
    INNER JOIN ACO.dbo.elgemp e ON m.ednmbr = e.eenmbr
    INNER JOIN ACO.dbo.elgdep ee ON e.eenmbr = ee.ednmbr AND ee.eddpcd = 'e'
    INNER JOIN ACO.dbo.clmubf p ON h.chclno = p.cwclno
    --INNER JOIN ACO.dbo.drghis? c ON h.chclno = c.diclno
    LEFT OUTER JOIN ACO.dbo.prvmst pp ON d.cdpvno = pp.pmnmbr
    LEFT OUTER JOIN ACO.dbo.prvmst ps ON d.cdspno = ps.pmnmbr
    LEFT OUTER JOIN ACO.dbo.prvnpi sn ON d.cdspno = sn.npinmbr
    LEFT OUTER JOIN ACO.dbo.prvnpi pn ON d.cdpvno = pn.npinmbr
    LEFT OUTER JOIN [RIMSReport].[dbo].[NPI] nps ON sn.npinpi = nps.NPI 
    LEFT OUTER JOIN [RIMSReport].[dbo].[NPI] npp ON pn.npinpi = npp.NPI

WHERE 
    h.chgpno = 'CTT0001'
    AND CAST(h.chinto as DATETIME) >= DATEADD(DAY, -7, GetDate())
                                                

GROUP BY
    h.chclno, h.chinto
    --m.edsexx, m.edbrdt