验证转换日期值是否大于另一个无效的转换日期值
验证转换日期值是否大于另一个无效的转换日期值
提问于 2017-02-14 00:54:04
我正在尝试检查我转换为日期值的VARCHAR值，然后检查转换后的值是否大于另一个值是否如预期的那样工作。
我已经附上了一张s.EFFECTIVE_DATE不大于t.StatusEffectiveDate的图片，以及为什么它会显示在我的结果集中。这就是我的问题。
下面是我的代码，其中where子句下面的粗体和语句如果出现问题：
DECLARE @peims2016SnapshotDate date = '2016-10-28'
SELECT t.StudentID, t.Status, s.status, t.StatusEffectiveDate, s.effective_date, t.enddate, @peims2016SnapshotDate as peimssnapshotdate, CONVERT(VARCHAR(10), DATEADD(day, -1, t.Enddate),101) as NewEndDate
FROM Mcsfwisdtest.FRA.dbo.vw_HHSurvey s
INNER JOIN Fwisddw.dbo.Focus_Export_Econ_DisadvantagedHHS_TEST t
ON s.CustomerID = t.StudentID
AND s.SiteID = LocationID
WHERE CAST(t.Enddate as date) > @peims2016SnapshotDate
**AND CAST(s.effective_date as date) > CAST(t.StatusEffectiveDate as date)**
AND t.[Status] = 'PAID' AND s.[Status] != 'PAID'
OR (t.[Status] = 'REDUCED' AND s.[Status] = 'FREE')
ORDER BY t.StudentID asc回答 1
Stack Overflow用户
发布于 2017-02-14 00:57:37
您需要使用正确的样式(在您的示例中是样式101)转换为date：
DECLARE @peims2016SnapshotDate date = '2016-10-28'
SELECT t.StudentID,
t.Status,
s.status,
t.StatusEffectiveDate,
s.effective_date,
t.enddate,
@peims2016SnapshotDate as peimssnapshotdate,
CONVERT(VARCHAR(10),DATEADD(DAY,-1,t.Enddate),101) as NewEndDate
FROM Mcsfwisdtest.FRA.dbo.vw_HHSurvey s
INNER JOIN Fwisddw.dbo.Focus_Export_Econ_DisadvantagedHHS_TEST t
ON s.CustomerID = t.StudentID
AND s.SiteID = LocationID
WHERE CAST(t.Enddate as date) > @peims2016SnapshotDate
AND CONVERT(date,s.effective_date,101) > CONVERT(date,t.StatusEffectiveDate,101)
AND ((t.[Status] = 'PAID' AND s.[Status] != 'PAID')
OR (t.[Status] = 'REDUCED' AND s.[Status] = 'FREE'))
ORDER BY t.StudentID asc;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42209370
复制相关文章
相似问题
腾讯云开发者
