?
Solved

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

Posted on 2008-11-04
4
Medium Priority
?
201 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:tcalbaz
  • 3
4 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22882362
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22882370
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
 
LVL 1

Author Closing Comment

by:tcalbaz
ID: 31513365
mwvisa1,

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

Ted
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22886231
You are very welcome!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question