UDF that returns 1 day before first day of the month (9 months ago)

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
---Created: 11/4/2008

CREATE FUNCTION [udf_9MonthsAgo]
     (@sdatDate DATETIME)
RETURNS DATETIME AS  
BEGIN
     DECLARE @pdatReturn DATETIME
     
     SELECT @pdatReturn = CONVERT(VARCHAR,YEAR(dateadd(mm,-10,@sdatDate))) + '-' + CONVERT(VARCHAR,MONTH(dateadd(mm,-10,@sdatDate))) + '-28 00:00:00'

     RETURN(@pdatReturn)
END


Thank you all!

Ted
LVL 1
tcalbazAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
In your UDF:
CREATE FUNCTION [udf_9MonthsAgo]
     (@sdatDate DATETIME)
RETURNS DATETIME AS  
BEGIN
     DECLARE @pdatReturn DATETIME
     
     SELECT @pdatReturn = DATEADD(mm, - 9, DATEDIFF(dd, 0, @sdatDate) - DAY(@sdatDate))
 
     RETURN(@pdatReturn)
END

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
This will get you '1/31/2008 12:00:00 AM' which would be 9 months to February 4th, then go to first day of month and one day previous.
SELECT DATEADD(mm, - 9, DATEDIFF(dd, 0, GETDATE()) - DAY(GETDATE()))

Open in new window

0
 
tcalbazAuthor Commented:
mwvisa1,

MOST EXCELLENT!!!!
It works perfectly.
Thank you!

Ted
0
 
Kevin CrossChief Technology OfficerCommented:
You are very welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.