asked
Create a Function for Start and End Dates
I want to create a function that returns a start and end date for the prior month. This will allow me as I create reports I can call that function to return my dates instead of always trying to create the code for the two dates. CREATE FUNCTION dbo.PriorMonthStartEnd () RETURNS DATETIME BEGIN DECLARE @StartDate DATE ,@EndDate DATE -- Prior month's 1st day SET @StartDate = CONVERT(VARCHAR(10),DATEADD(m,-1, Dateadd(d,1-DATEPART(d,getdate()),GETDATE())), 101) -- Prior month's last day SET @EndDate = CONVERT(varchar(10), dateadd(mm,-1,DATEADD(dd,-DAY(getdate()),DATEADD(mm,1,getdate()))), 101) RETURN @StartDate RETURN @EndDate END
You should perhaps think about your return datatypes. You have in your function above declared it to return datetime datatype, but you try to return variables which have date datatype. But to your problem: You should return a table, not a scalar value. That's something you should get into the habit of doing even when you only need a scalar value returned. From a performance perspective, a scalar valued UDF is a disaster, while a table valued UDF could be OK from a performance standpoint. For it to be OK, it should be an inline table valued UDF - meaning it has no BEGIN/END-block, instead only a return-statement containing a SELECT query. Something like this: CREATE FUNCTION dbo.PriorMonthStartEnd() RETURNS table AS RETURN (SELECT
,); GO If you wonder why scalar valued UDFs are bad, Hugo Kornelis has the answers for you. http://sqlblog.com/blogs/hugo_kornelis/archive/2012/05/20/t-sql-user-defined-functions-the-good-the-bad-and-the-ugly-part-1.aspx
You could use the techniques shown by Pinal Dave in his article at
http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/ You'll just need to change the SELECT @mydate = GETDATE() to SELECT @mydate = DATEADD(month, -1, GETDATE())
Also keep in mind how you are going to use the values. DATE values, when compared with a DATETIME, will be considered to have a 00:00:00 time value. This can lead to problems for events that happen on the last day of your chosen time period... You code currently returns 2013-Feb-01 and 2013-Feb-28 so we will work with that. DECLARE @Dates TABLE (DateVal DATETIME) INSERT @Dates ( [DateVal] ) VALUES ( '2013-Feb-01 09:44:38' ),( '2013-Feb-20 09:44:38' ),( '2013-Feb-28 09:44:38' ) SELECT * FROM @Dates AS d WHERE [d].[DateVal] BETWEEN @StartDate AND @EndDate The '2013-Feb-28 09:44:38' will not be included as it is after '2013-Feb-28 00:00:00'.
14 People are following this question.
