SQL date calculation - 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)

SQL date calculation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-01 : 07:36:03

Paul writes "I have a column in my table, DOB datetime, for the Date of Birth of a person.

How do I go about calculating the number of days (from today)until the persons NEXT birthday?"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-07-01 : 08:17:07

Pretty straight forward, really ... You may want to handle today's birthdays a bit differently ...


declare
    @dob datetime

select
    @dob = '06/01/1949'

select
    case 
        when dateadd(yy,datediff(yy,@dob,getdate()),@dob) > getdate() 
            then datediff(dd,getdate(),dateadd(yy,datediff(yy,@dob,getdate()),@dob))
        when dateadd(yy,datediff(yy,@dob,getdate()),@dob) = dateadd(dd,datediff(dd,0,getdate()),0)
            then 0
        else datediff(dd,getdate(),dateadd(yy,datediff(yy,@dob,getdate())+1,@dob))
    end as 'Days till next birthday'

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources