how to filter by dates after the current date?

Question

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.

This answer should help you https://stackoverflow.com/questions/35576983/how-to-create-a-date-in-sql-server-given-the-day-month-and-year-as-integers — Tree Frog, May 21 '19 at 11:23
Possible duplicate of [How to create a Date in SQL Server given the Day, Month and Year as Integers](https://stackoverflow.com/questions/35576983/how-to-create-a-date-in-sql-server-given-the-day-month-and-year-as-integers) — sticky bit, May 21 '19 at 11:31
I would suggest having a computed column that has the actual date calculated. if you make the column `PERSISTED`, you can even index it. If you think about it logically, why would you expect Jan-Apr 2020 to show up? 1, 2, 3 and 4 are all less than or equal to 4, and you **only** want rows where the month has a value greater than 4. — Thom A, May 21 '19 at 11:37

score 0 · Answer 1 · answered May 21 '19 at 11:38

If you use sql server 2012+ You can get the current data date as follow

datefromparts(exp0yr, exp0mo, exp0da)

Otherwise use some thing like this:

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

Your script becomes some thing like this

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

score 0 · Answer 2 · answered May 21 '19 at 11:47

Try this one

--Temp table 
CREATE TABLE #Test
(cYear  VARCHAR(4) NOT NULL, 
 cMonth VARCHAR(2) NOT NULL , 
 cDay   VARCHAR(2) NOT NULL 
);
--Sample data
INSERT INTO #Test
       SELECT '2019', 
              '04', 
              '01'
       UNION ALL
       SELECT '2018', 
              '06', 
              '01';
--Calculated column - you can define prefix, such as leading zeros
ALTER TABLE #TEST
ADD calcDate AS CYEAR + CMONTH +  CDAY PERSISTED


--Shows only one record
SELECT *
FROM #Test
WHERE calcDate BETWEEN '20190101' AND '20191231';

how to filter by dates after the current date?

2 Answers2