Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Function to return a date

I have a function attached that is not returning the correct information.

If I use the comments out section...it's a year too much
If I use as is...it MAY be a year not enough


So...
I want to pass in a date and a month integer as variables

What I want in return is to take the date that's passed in
Find the very next date that the month passed in occurs
The go to the very next FIRST of the month PLUS 3 more months

Example
If I pass in 2012-11-01 00:00:00, 12

The very next time the 12th month occurs is 2012-12-01 00:00:00
Go to the NEXT first of the month
2013-01-01 00:00:00
Add 3 months
2013-04-01 00:00:00

The function below returns either
2012-04-01 00:00:00.000 if I use the bottom part or

2014-03-01 00:00:00.000
if I use the top part
-- ==================================================
-- Author:		lrbrister
-- Create date: 12.08.2010
-- Description:	Date Formatter
--  Select dbo.getLIFileDate('2012-11-01 00:00:00', 12)
-- ==================================================
ALTER function [dbo].[getLIFileDate](@dt datetime,@month int)
Returns datetime as
Begin
--	Declare @retDate datetime
--	Set @retDate = dateadd(m, @month+4, dateadd(d,  -datepart(d, @dt)+1, @dt))
--	Return @retDate
	Declare @addYear INteger
	Set @addYear = 0
	If Month(@dt) > @month set @addYear = 1
	Declare @retDate datetime
	Set @retDate = convert(char(8),YEAR(getdate())*10000+@month*100+1)
	If @retDate <= GETDATE() 
		Set @retDate = dateadd(yy,1,@retDate)
		Set @retDate = DATEADD(M,4,@retDate)
	If @retDate < @dt 
		Set @retDate = dateadd(yy, datediff(yy, @retDate, @dt), @retDate)
 
		Set @retDate = dateadd(yy,  @addYear, @retDate)
	Return @retDate
End

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of arilani
arilani

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ephraim Wangoya
Basically you are adding four months to the month you pass in to the function


-- Create date: 12.08.2010
-- Description:      Date Formatter
--  Select dbo.getLIFileDate('2012-11-01 00:00:00', 12)
-- ==================================================
ALTER function [dbo].[getLIFileDate](@dt datetime,@month int)
Returns datetime as
Begin
      Declare @retDate datetime
      Set @retDate = cast(@month as varchar) + '/01/' + cast(DATEPART(yy, @dt) as varchar)
      set @retDate = DATEADD(mm, 4, @retdate)
      
      Return @retDate
End
Avatar of Larry Brister

ASKER

arilani:
That was exactly what I needed thanks
Perfect...thanks
Avatar of arilani
arilani

you're welcome