Devoted SQL Gurus.
I'm creating a rolling 9 month report. So I need a UDF function that bases itself on the current date and returns a date 9 months ago and one day before the beginning of that month. That means if today is November 4th my criteria must return all records with a start date greater then one day before February ie: January 31 00:00:00 I kind of cheated and created a UDF that returns a date based on the 28th of the previous month but that's kind of crude. Your help is greatly appreciated.
--Actually cheats a little.
--Uses 2 or 3 days before end of previous month
CREATE FUNCTION [udf_9MonthsAgo]
RETURNS DATETIME AS
DECLARE @pdatReturn DATETIME
SELECT @pdatReturn = CONVERT(VARCHAR,YEAR(dateadd(mm,-10,@sdatDate))) + '-' + CONVERT(VARCHAR,MONTH(dateadd(mm,-10,@sdatDate))) + '-28 00:00:00'
Thank you all!