Seleting dates in rolling year

[IanWaterman]

Hi

Using 2008

I need to write a query which is to be run at the beginning of each month and bring back records from previous 12 month.

ie if I run any time during March 2010, it should select records with dates in range

1 March 2009 - 28 Feb 2010, catering for leap years too.
when I run in April 2010 must bring back 1st April 2009 to 31 March 2010 and so on.

I know I can use Datediff to get me back one year but can not fathom how to then derive the 1st of that month?.

Also need logic to find last day of previous month. I can do this with Crystal logic but do not know enough MS SQL to replicate.

Its not guaranteed to run on Ist of month so I can't use getdate() - 1 to determine last day of previous month.

Thank you

Ian

 

[markros]

Please see this link

http://wiki.lessthandot.com/index.php/How_to_find_the_first_and_last_days_in_years,_months_etc

PluralSight Learning Library

 

[IanWaterman]

Thanks for this.

I can use

DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0) AS FirstDayOfMonth, and select dates less than that eg

"calldetails"."dentered" < DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)

I then tried using dateadd to subject a year for my start date but something goes wrong and no data is returned.

"calldetails"."dentered" >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))

Is there something wrong with either my syntax or logic?

Ian

 

[bborissov]

With DATEADD(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))you get the first day of the current month but one year ago (i.e. 01 Mar 2009) is that what you want?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[IanWaterman]

Yes that is what I want but query is not returning any data.

There is definitely data there!

Ian

 

[IanWaterman]

DOH!

I am using this query in a Command within a Crystal Report and I forgot that within the select expert I already had a date restriction.

When that was removed query worked perfectly.

Thanks for your input.

Ian

 

[simian336]

You might also consider a Calendar table.

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Simi