Date filter WHERE clause

Question

Post reply

Date filter WHERE clause

mediacommentry

SSC Veteran

Points: 268
More actions
February 1, 2019 at 1:03 am

#370392

Hi,

I have this situation, we are now on month FEB, I want data from Jan this year, even in DECEMBER I would still need to pull data from JAN this year, January next year I would still need data from 1 JAN 2019, BUT from FEB 2020, I want data from JAN 2020. So I basically want data from JAN on the current year provided we are on second month or more, if we are on the first month of the year, I want from Jan the previous year.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 23842 More actions · Answer 1

mediacommentry - Friday, February 1, 2019 1:03 AM

Hi,

I have this situation, we are now on month FEB, I want data from Jan this year, even in DECEMBER I would still need to pull data from JAN this year, January next year I would still need data from 1 JAN 2019, BUT from FEB 2020, I want data from JAN 2020. So I basically want data from JAN on the current year provided we are on second month or more, if we are on the first month of the year, I want from Jan the previous year.

You could try something like this ...
WHERE [YourDateField] >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -IIF(MONTH(GETDATE()) = 1,1,0), 0)

How to post data/code on a forum to get the best help.

mediacommentry SSC Veteran Points: 268 More actions · Answer 2

DesNorton - Friday, February 1, 2019 1:28 AM

mediacommentry - Friday, February 1, 2019 1:03 AM

Hi,

I have this situation, we are now on month FEB, I want data from Jan this year, even in DECEMBER I would still need to pull data from JAN this year, January next year I would still need data from 1 JAN 2019, BUT from FEB 2020, I want data from JAN 2020. So I basically want data from JAN on the current year provided we are on second month or more, if we are on the first month of the year, I want from Jan the previous year.

You could try something like this ...
WHERE [YourDateField] >= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -IIF(MONTH(GETDATE()) = 1,1,0), 0)

Thank you.

Luis Cazares SSC Guru Points: 183686 More actions · Answer 3

This is another way of doing it.
WHERE [YourDateField] >= DATEADD( YY, DATEDIFF( YY, 0, DATEADD( mm, -1, GETDATE())), 0)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2