Is there any best way to GetNextAnniversaryDate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Is there any best way to GetNextAnniversaryDate

Author

Topic

hey001us
Posting Yak Master

185 Posts

Posted - 2008-11-10 : 21:03:49

Is there any best way to get next anniversary date?
USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetNextAnniversaryDate]
(
@JoinDate AS DATETIME, @CurrentDate AS DATETIME
)
RETURNS VARCHAR(50) AS
BEGIN

DECLARE @LastDayOfMonth AS INT, @JoinDay AS INT, @Today AS INT
, @CurrentMonth AS INT, @CurrentYear AS INT
, @NextAnniversaryDay AS INT, @NextAnniversaryMonth AS INT
, @NextAnniversaryYear AS INT

SELECT @LastDayOfMonth = Day(DateAdd(mm, 1, @CurrentDate - Day(@CurrentDate) + 1) - 1)

SELECT @JoinDay = DatePart(Day, @JoinDate)
,@CurrentMonth = DatePart(Month, @CurrentDate)
,@CurrentYear = DatePart(Year, @CurrentDate)
,@NextAnniversaryYear = @CurrentYear
,@NextAnniversaryDay = @JoinDay

SELECT @Today = DatePart(Day, @CurrentDate)
If (@Today > @NextAnniversaryDay)
BEGIN
--get next month date
SELECT @NextAnniversaryMonth = @CurrentMonth + 1

If @CurrentMonth = 12
BEGIN
SELECT @NextAnniversaryMonth = 1
SELECT @NextAnniversaryYear = @CurrentYear + 1
END

If @JoinDay > @LastDayOfMonth
BEGIN
SELECT @NextAnniversaryDay = @LastDayOfMonth
END
END
Else
BEGIN

If @NextAnniversaryDay > @LastDayOfMonth
BEGIN
SELECT @NextAnniversaryDay = @LastDayOfMonth
END
SELECT @NextAnniversaryMonth = @CurrentMonth
END
SELECT @JoinDate = RTRIM(Cast(@NextAnniversaryMonth AS CHAR(2))) + '/' + RTRIM(Cast(@NextAnniversaryDay AS CHAR(2))) +'/' + Cast(@NextAnniversaryYear AS CHAR(4))
RETURN @JoinDate
END

hey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 02:06:21

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111271

hey001us
Posting Yak Master

185 Posts

Posted - 2008-11-11 : 14:22:22

Hi,
Thanks for that. I have seen already the above URL but its not matching with my requirement. Its ok, I am rewriting my won.

Cheers

hey

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-11 : 15:54:37

This seems like a simple solution.

declare @JoinDate 	datetime
declare @Curdate	datetime

set @JoinDate = '20001112'
set @Curdate = getdate()

select	top 1
	NextAnniversaryDate =
		dateadd(yy,datediff(yy,j.JoinDate,c.Curdate)+a.YearOffset,j.JoinDate)
from
	-- Offset for this year and next year
	( select YearOffset = 0 union all select YearOffset = 1) a
	cross join
	-- Set Curdate to midnight
	( select Curdate = dateadd(dd,datediff(dd,0,@Curdate),0) ) c
	cross join
	-- Set JoinDate to midnight
	( select JoinDate = dateadd(dd,datediff(dd,0,@JoinDate),0) ) j
where
	dateadd(yy,datediff(yy,j.JoinDate,c.Curdate)+a.YearOffset,j.JoinDate) >= c.Curdate
order by
	1 asc


Results:

NextAnniversaryDate
------------------------
2008-11-12 00:00:00.000

(1 row(s) affected)

CODO ERGO SUM

hey001us
Posting Yak Master

185 Posts

Posted - 2008-11-11 : 20:03:09

Hi MVJ,

Thanks for the solutions. It’s really simple, but the results is wrong if the @JoinDate = '2006-10-31'.

The result is shows ‘2009-10-31 00:00:00.000’ instead ‘2008-11-30 00:00:00.000’
Cheers

hey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources