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(datea
RETURN(@pdatReturn)
END
Thank you all!
Ted
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(datea
RETURN(@pdatReturn)
END
Thank you all!
Ted
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mwvisa1,
MOST EXCELLENT!!!!
It works perfectly.
Thank you!
Ted
MOST EXCELLENT!!!!
It works perfectly.
Thank you!
Ted
You are very welcome!
Open in new window