Rolling 12 Months Date Query 2

[southbean]

Hello All,

I’m having trouble writing a WHERE statement that returns the records I want.

What I need is a rolling 12 months. That is, if I ran the query today I want returned records where the value of the date field is 02/01/2006 – 01/31/2007.

In my WHERE clause I have:

DateDiff(m,tbl.datetime,GetDate()) <= 12)

This returns records where the date field value is 02/01/2006 – 02/21/2007 (current date).

My SQL is (obviously) rudimentary. Could someone please remedy my ignorance here?

How can I get records where the value of the date field is 02/01/2006 – 01/31/2007?


Thanks for any/all suggestions!

- tm

 

[bborissov]

DECLARE @CurrentDate datetime
SET @CurrentDate = DateAdd(ss,-1,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

....
WHERE DateDiff(m,tbl.datetime,@CurrentDate) <= 12)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[earthandfire]

You could try

[tt]WHERE tbl.datetime >= (DATEADD(m, -12, GetDate()) + 1)[/tt]


Hope this helps.

 

[southbean]

Hi bborissov,

Thanks for your reply!

I tried your suggestion but got the same result: records returned from 02/01/2006 to 02/21/2007.

I'm trying to get records from 02/01/2006 to the last day of the previous month (01/31/2007).

Any other suggestions?

Thanks again for your prompt reply!!!

- tm

 

[mharroff]

Try changing Bborissov's code to this:

DECLARE @CurrentDate datetime
SET @CurrentDate = DateAdd(ss,-1,DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0))

....
WHERE DateDiff(m,tbl.datetime,@CurrentDate) Between 1 and 12)

 

[earthandfire]

Sorry thatt should have been without the + 1:

[tt]>= DATEADD(m, -12, GetDate())[/tt]


Hope this helps.

 

[southbean]

Hi mharroff,

Bingo! That worked perfectly.

Thanks VERY much. I really appreciate it!

- tm

 

[southbean]

Hi earthandfire,

Thanks for your prompt response!

However, I got the same result: records returned from 02/01/2006 to 02/21/2007.

Thanks just the same!

- tm

 

[gmmastros]

This is the date range you are looking for:

Select DateAdd(Year, -1, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) - 1) + 1,
       DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) - 1

Copy paste this to query analyzer to verify this.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[southbean]

Hi gmmastros,

Thanks for your post.

Yes, that returns the date range I need, but how do I use this code relative to my date field?

Thanks again,

- tm

 

[gmmastros]

Like this...

Where tbl.DateTime >= DateAdd(Year, -1, DateAdd(Month, DateDiff(Month, 0, GetDate()), 0) - 1) + 1
             And tbl.DateTime < DateAdd(Month, DateDiff(Month, 0, GetDate()), 0)

Notice that I removed the -1 on the end range, but also notice that I changed it to a less than operator.

So... If the last date you want is 1-31-2007, this can be accomplished with < 2-1-2007.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[southbean]

Hi gmmastros,

[!]Brilliant![/!]

I was blind, and now I see.

Thanks VERY much!

- tm

 

[gmmastros]

You are [!]very[/!] welcome.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom