Sam Sam - 6 months ago 8
SQL Question

Get Data between two months with different years in SQL

I got a table in sql server which has several fields including expYear in int, expMonth in int.

I am writing a SP to retrieve data between two months. which i can do by

(expMonth between @M1 and @M2) AND @currentYear=expYear
if both M1 and M2 in the same
expYear
.

But
if my M1=11(december)
which is in the year 2016
and M2=2(february)
which is in the year 2017 then it doesn't work.

Could anyone tell what to do? Please remember, in DB both
"expYear "
and
"expMonth "
value are stored as int. even
"@M1"
,
"@M2"
,
"@currentYear"
all are coming from back end as int.

Answer

You can build proper dates from your columns and input and compare them like this:

SELECT <ColumnsList>
FROM <TableName>
WHERE   CAST(RIGHT('0000' + CAST(expYear as varchar(4)), 4) + '-' + RIGHT('00' + CAST(expMonth as varchar(2)), 2) +'-01' As Date)
        BETWEEN CAST(RIGHT('0000' + CAST(@currentYear as varchar(4)), 4) + '-' + RIGHT('00' + CAST(@M1  as varchar(2)), 2) +'-01' As Date)
        AND CAST(RIGHT('0000' + CAST(@currentYear as varchar(4)), 4) + '-' + RIGHT('00' + CAST(@M2 as varchar(2)), 2) +'-01' As Date)
Comments