SQL Query: Moving 12-month Data Window

[Kenny100]

Hi Folks

Just starting out in the world of SQL and am having a problem with dates. I have the following in a table called tblData:

Date,Data
01/01/2004,123123123
01/02/2004,123123123
01/03/2004,123123123
01/04/2004,123123123
01/05/2004,123123123
01/06/2004,123123123
01/07/2004,123123123
01/08/2004,123123123
01/09/2004,123123123
01/10/2004,123123123
01/11/2004,123123123
01/12/2004,123123123
01/01/2005,123123123
01/02/2005,123123123
01/03/2005,123123123

I want to create an SQL query that will return all rows with a date within the last 12 months. So, with the above example, I want to see the following result (taking the current date to be 31/03/2005):

01/03/2004,123123123
01/04/2004,123123123
01/05/2004,123123123
01/06/2004,123123123
01/07/2004,123123123
01/08/2004,123123123
01/09/2004,123123123
01/10/2004,123123123
01/11/2004,123123123
01/12/2004,123123123
01/01/2005,123123123
01/02/2005,123123123
01/03/2005,123123123

I created a query that gives me the current date but am not sure if it helps me with this:

SELECT Today=convert(varchar, GETDATE(), 101)

I also know that DATEDIFF could help but I'm finding it impossible to string all this together into a SQL query that works! Is anyone able to help?

Thanks!

 

[vongrunt]

This fits your description (12 months prior to current one until today)

declare @from smalldatetime, @to smalldatetime

set @to = dateadd(dd, datediff(dd, 0, getdate()), 0)
set @from = dateadd(mm, datediff(mm, 0, dateadd(yy, -1, @to)), 0)

select [Date], Data
from tblData
where [Date] between @from and @to
order by [Date]

Alternatively you can use varchar(8) for date constants (preferably in ISO format) then rely upon implicit conversion:

declare @from varchar(8), @to varchar(8)

set @to = convert(varchar(8), getdate(), 112)
set @from = convert(varchar(4), left(@to, 4)-1) + substring(@to, 5, 2)+ '01'

<same SELECT here>

If you are 100% sure that [Date] is never greater than today, this can simplify expressions a bit (@to is not necessary for final query).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[Kenny100]

Thanks vongrunt. I came up with the following query that also seems to work. What additional benefits do your queries offer over mine? I'm not trying to say "my one is better" or anything! I'm just interested to know as I'm very new to SQL and need to keep increasing my knowledge!

SELECT *
FROM tblData
WHERE [Date] BETWEEN dateadd(m, -12, getdate()) AND getdate()