Last Birthday - SQL Server Forums

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

Transact-SQL (2000)

Last Birthday

Author

Topic

webforyou
Starting Member

15 Posts

Posted - 2007-10-03 : 13:18:28

Hi all,
Is there any function to calculate the last birthday in SQL 2000?
In Excel we have the yearfrac function to do this.

Ex: First Birthday '1980-12-25'
To now '2007-10-04', which last birthday?
-> '2006-12-25'

I search the forum but I just find the topic about date calculation, date diffrent.

Thanks!

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-10-03 : 14:14:09

[code]create function [dbo].[LastAnniversaryAsOf](@Anniversary datetime, @AsOfDate datetime)
returns datetime as
begin
/*
------------------------------------------------------------------------------------------------------------------------
FUNCTION: LastAnniversaryAsOf
DESCRIPTION: Returns the last anniversary as of a specified date.
------------------------------------------------------------------------------------------------------------------------
REVISION HISTORY:
EDITOR DATE REVISIONS
blindman 10/3/2007 Function created
------------------------------------------------------------------------------------------------------------------------
*/

--Test parameters
--declare @Anniversary datetime
--declare @AsOfDate datetime
--set @Anniversary = '6/10/1965'
--set @AsOfDate = getdate()

return
(select max(Anniversary) as LastAnniversary
from
(select dateadd(year, datediff(year, @Anniversary, @AsOfDate)-1, @Anniversary) as Anniversary
UNION
select dateadd(year, datediff(year, @Anniversary, @AsOfDate), @Anniversary) as Anniversary) Candidates
where Anniversary < @AsOfDate)

end
go

create function [dbo].[NextAnniversaryAsOf](@Anniversary datetime, @AsOfDate datetime)
returns datetime as
begin
/*
------------------------------------------------------------------------------------------------------------------------
FUNCTION: NextAnniversaryAsOf
DESCRIPTION: Returns the Next anniversary as of a specified date.
------------------------------------------------------------------------------------------------------------------------
REVISION HISTORY:
EDITOR DATE REVISIONS
blindman 10/3/2007 Function created
------------------------------------------------------------------------------------------------------------------------
*/

--Test parameters
--declare @Anniversary datetime
--declare @AsOfDate datetime
--set @Anniversary = '6/10/1965'
--set @AsOfDate = getdate()

return
(select min(Anniversary) as NextAnniversary
from
(select dateadd(year, datediff(year, @Anniversary, @AsOfDate)+1, @Anniversary) as Anniversary
UNION
select dateadd(year, datediff(year, @Anniversary, @AsOfDate), @Anniversary) as Anniversary) Candidates
where Anniversary > @AsOfDate)

end
[/code]

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources