CODEWITHSUNDEEP
sqlsql-serverdate
Sheri G
Sheri G

Reputation: 47

Need dynamic dates in my SQL where clause

I need to write a (my first) simple stored procedure in SQL Server 2012 that will run on the 1st of every month. The data needs to be for the last 3 full months every time it runs.

I need the WHERE clause to be based on a table.STARTDATE that falls between the dates of the last 3 months.

So when it runs July 1st, it needs to be: 

WHERE t.STARTDATE BETWEEN '2018-04-01' AND '2018-06-30'

and when it runs on August 1st, it needs to be: 

WHERE t.STARTDATE BETWEEN '2018-05-01' AND '2018-07-31'

and so on.

OK, I just tried this and can't really validate it until the last day of the month. Would this work? 

BETWEEN DATEADD(m, -3, GETDATE()) AND DATEADD(dd, -1, GETDATE())

Upvotes: 1

Views: 2596

Answers (4)

Mani Deep
Mani Deep

Reputation: 1356

you can do it by defining dates, here getdate() will be your table.STARTDATE

declare @myStartdate date=dateadd(MM, -3,getdate())
declare @myEnddate date=dateadd(MM, -1,getdate())

SELECT getdate() as today, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@myStartdate)-1),@myStartdate),101) AS Date_start_Value,
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@myEnddate))),
DATEADD(mm,1,@myEnddate)),101) as date_end_value

this will return 

today                    Date_start_Value   date_end_value

2018-06-20 21:34:07.180  03/01/2018          05/31/2018

now you can use above in your where clause

WHERE t.STARTDATE BETWEEN 
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@myStartdate)-1),@myStartdate),101) 
AND 
CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@myEnddate))),
DATEADD(mm,1,@myEnddate)),101)`

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271181

I think you want:

where t.STARTDATE >= dateadd(month, -3, datefromparts(year(getdate()), month(getdate()), 
1)) and
      t.STARTDATE < datefromparts(year(getdate()), month(getdate()), 1)

For reasons why you should not get in the habit of using between with dates, I refer you to Aaron Bertrand's blog, What do between and the devil have in common?

Upvotes: 1

Amrita Srivastava
Amrita Srivastava

Reputation: 384

One way for doing this is using a CTE. This link will be useful to you..

TSQL loop months in sequence

Upvotes: 1

ccarpenter32
ccarpenter32

Reputation: 1077

You have it correctly (assuming time codes do not matter).

Just as an FYI when doing this sort of thing, you have to remember that you can force dates into the code to test it.

As such:

SELECT dateadd(m,-3,'08/01/2018'), dateadd(dd,-1,'08/01/2018')
SELECT dateadd(m,-3,'07/01/2018'), dateadd(dd,-1,'07/01/2018')

Which would test it for July 1st and August 1st (your theorectical date)

Upvotes: 1

Related Questions