时间戳

首页 > 解决方案 > how to filter by dates after the current date?

sql-server - how to filter by dates after the current date?

问题描述

I want to filter expiration dates by dates after the current date. The problem is that the current data's date is in three columns; exp0year, exp0mo and exp0da.

I have tried to put multiple where clauses, the problem with say exp0mo > 4disqualifies a date like 1/1/2020.

Select exp0yr,exp0mo,exp0da,policy0num,zip0post,add0line01,add0line03,add0line04,profit0ctr
from ni.pmsp0200
where exp0yr>118  and exp0mo>04 and exp0da>20 and profit0ctr='eoh'
group by exp0yr,exp0mo,exp0da,policy0num,zip0post,add0line01,add0line03,add0line04,profit0ctr

I expected to get dates after 05/21/2019 but instead got all of those date except from 01/01/2020-05/20/2020.

标签： sql-serverwhere-clause

解决方案

如果你使用 sql server 2012+ 你可以得到当前数据日期如下

datefromparts(exp0yr, exp0mo, exp0da)

否则使用这样的东西：

 SELECT CAST(CONCAT(CAST(@exp0yr AS VARCHAR(4)), '-',CAST(exp0mo AS VARCHAR(2)), '-',CAST(exp0daAS VARCHAR(2))) AS DATE)

你的脚本变成了这样

declare @mydate as date
set @mydate='2019/05/21' --Put your target date here
    Select exp0yr,exp0mo,exp0da,policy0num,zip0post,add0line01,add0line03,add0line04,profit0ctr
    from ni.pmsp0200
    where datefromparts(exp0yr, exp0mo, exp0da)>@mydate and profit0ctr='eoh'
    group by exp0yr,exp0mo,exp0da,policy0num,zip0post,add0line01,add0line03,add0line04,profit0ctr

推荐阅读