Can I set the time range to get 3 month data in SQL script? 1

[klearn98]

Hi,
I have a script that need to be run automatically in the first day of each month and get the previous 3 month data. How can I write a SQL expression to set the time range? For example, if the script runs on 5/1/2013, the data should include 02/01/2013 to 04/30/2013. Now I am doing it this way:
pp.timestamp BETWEEN DATEADD(DAY,-90,GETDATE()) AND DATEADD(DAY,-1,GETDATE()) )
But the problem is that it won’t be accurate when the month has 31 days or 28 days.
How can I make it more accurate?
Thank you for any ideas and suggestions!!!

 

[gmmastros]

You can use this code to get the first day of the month, 3 months ago.

Select DateAdd(Month, -3 + DateDiff(Month, 0, GetDate()), 0)

When you run the code shown above, it will return Feb 1 for any day that you run it in may.

I have a question for you though.... the values stored in pp.timestamp, do they ONLY contain a date component, or is there time parts in there too? The reason I ask is that your current code would not include all of the data on the last day of the month. Specifically this:

DATEADD(DAY,-1,GETDATE())

GetDate() returns a date and time. When you subtract 1 day, you still have a time component. Running that code right now produces "2013-05-21 11:01:11.550".

I would suggest this:

pp.timestamp >=  DateAdd(Month, -3 + DateDiff(Month, 0, GetDate()), 0)
And pp.timestamp <  DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

If you run this:

Select  DateAdd(Month, -3 + DateDiff(Month, 0, GetDate()), 0),
        DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

You will get:
2013-02-01 00:00:00.000
2013-05-01 00:00:00.000

So... timestamp >= Feb 1 and timestamp < may 1

The "less than may 1" would include any value at any time on the last day of April.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[DaveInIowa]

Would it be as easy as?

DECLARE @Date DATE = GETDATE()

pp.timestamp BETWEEN DATEADD([red]MONTH[/red], -3, @Date) AND DATEADD(DAY, -1, @Date)

 

[klearn98]

George and DaveInIowa,

Thank you so much to both of you!!!
Both codes work well.
DaveInIowa's code is simple and easy. But like George mentioned, the data type of my timestamp is datatime, so subtract day might not get all data in that date.
So I will do
pp.timestamp >= DateAdd(Month, -3 + DateDiff(Month, 0, GetDate()), 0)
And pp.timestamp < DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)
Again, thanks a lot!!

 

[gmmastros]

The added benefit of my advice is that you can run the code on any day in may and it will select that data from Feb 1 through end of April. This means that if there is a problem with the scheduled job and you don't notice for a couple days, you can still run the code and get exactly the same results.

Anyway... I'm glad to have helped.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[klearn98]

Yes, I realized that.
Thank you!!