Calculate days till next birthday

Hi,
I'm building an aplication in with I need to calculate the number of days
till the persons next birthday. I already have an UDF that calculates the age
given the birthdate and the current date, and I would like to be able to do
an UDF that calculates the number of days till next birthday to use it on a
computed field.
Look at DATEDIFF system funtion in the BOL
"M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> Hi,
> I'm building an aplication in with I need to calculate the number of days
> till the persons next birthday. I already have an UDF that calculates the
age
> given the birthdate and the current date, and I would like to be able to
do
> an UDF that calculates the number of days till next birthday to use it on
a
> computed field.
>
|||I have, i've used it to build the other UDF I reffered. But this one isn't
thar simple. I have a PHP Script that solves this problem, it's something
like this:
"
$nextbirthday =
mktime(0,0,0,"09","12",date("Y"));
// if $nextbirtday is in the past, add 1 year to $nextbirthday
if ($nextbirthday<$nowdate) {
$nextbirthday=$nextbirthday+(60*60*24*365);
}
// calculate the remaining day's till my next birthday
$daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
echo " My next birthday is over $daycount day's.";
"
Now I want to transform this into an UDF, to be able to use it on a computed
field.
"Uri Dimant" wrote:

> Look at DATEDIFF system funtion in the BOL
>
> "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> age
> do
> a
>
>
|||Hi
Did u try it this way:
DATEDIFF(d, getdate(), birthdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Márcio Santos" wrote:
[vbcol=seagreen]
> I have, i've used it to build the other UDF I reffered. But this one isn't
> thar simple. I have a PHP Script that solves this problem, it's something
> like this:
> "
> $nextbirthday =
> mktime(0,0,0,"09","12",date("Y"));
> // if $nextbirtday is in the past, add 1 year to $nextbirthday
> if ($nextbirthday<$nowdate) {
> $nextbirthday=$nextbirthday+(60*60*24*365);
> }
> // calculate the remaining day's till my next birthday
> $daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
> echo " My next birthday is over $daycount day's.";
> "
> Now I want to transform this into an UDF, to be able to use it on a computed
> field.
>
> "Uri Dimant" wrote:
|||On Wed, 8 Jun 2005 03:46:02 -0700, "Mrcio Santos" <Mrcio
Santos@.discussions.microsoft.com> wrote:

>Hi,
>I'm building an aplication in with I need to calculate the number of days
>till the persons next birthday. I already have an UDF that calculates the age
>given the birthdate and the current date, and I would like to be able to do
>an UDF that calculates the number of days till next birthday to use it on a
>computed field.
>
Hi Mrcio,
It doesn't look pretty and I have the feeling that there should be an
easier way, but this code will at least get the job done:
DECLARE @.BirthDay smalldatetime
SET @.BirthDay = 'yyyymmdd' -- Insert test data here
SELECT DATEDIFF(day,
CURRENT_TIMESTAMP,
DATEADD(year,
DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
+ CASE WHEN DATEADD(year, DATEDIFF(year,
@.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
ELSE 0 END,
@.BirthDay))
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thx... it works perfectly...
"Hugo Kornelis" wrote:

> On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
> Santos@.discussions.microsoft.com> wrote:
>
> Hi Márcio,
> It doesn't look pretty and I have the feeling that there should be an
> easier way, but this code will at least get the job done:
> DECLARE @.BirthDay smalldatetime
> SET @.BirthDay = 'yyyymmdd' -- Insert test data here
> SELECT DATEDIFF(day,
> CURRENT_TIMESTAMP,
> DATEADD(year,
> DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
> + CASE WHEN DATEADD(year, DATEDIFF(year,
> @.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
> ELSE 0 END,
> @.BirthDay))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Calculate days till next birthday

Hi,
I'm building an aplication in with I need to calculate the number of days
till the persons next birthday. I already have an UDF that calculates the ag
e
given the birthdate and the current date, and I would like to be able to do
an UDF that calculates the number of days till next birthday to use it on a
computed field.Look at DATEDIFF system funtion in the BOL
"M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> Hi,
> I'm building an aplication in with I need to calculate the number of days
> till the persons next birthday. I already have an UDF that calculates the
age
> given the birthdate and the current date, and I would like to be able to
do
> an UDF that calculates the number of days till next birthday to use it on
a
> computed field.
>|||I have, i've used it to build the other UDF I reffered. But this one isn't
thar simple. I have a php Script that solves this problem, it's something
like this:
"
$nextbirthday =
mktime(0,0,0,"09","12",date("Y"));
// if $nextbirtday is in the past, add 1 year to $nextbirthday
if ($nextbirthday<$nowdate) {
$nextbirthday=$nextbirthday+(60*60*24*36
5);
}
// calculate the remaining day's till my next birthday
$daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
echo " My next birthday is over $daycount day's.";
"
Now I want to transform this into an UDF, to be able to use it on a computed
field.
"Uri Dimant" wrote:

> Look at DATEDIFF system funtion in the BOL
>
> "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> age
> do
> a
>
>|||Hi
Did u try it this way:
DATEDIFF(d, getdate(), birthdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Márcio Santos" wrote:
[vbcol=seagreen]
> I have, i've used it to build the other UDF I reffered. But this one isn't
> thar simple. I have a php Script that solves this problem, it's something
> like this:
> "
> $nextbirthday =
> mktime(0,0,0,"09","12",date("Y"));
> // if $nextbirtday is in the past, add 1 year to $nextbirthday
> if ($nextbirthday<$nowdate) {
> $nextbirthday=$nextbirthday+(60*60*24*36
5);
> }
> // calculate the remaining day's till my next birthday
> $daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
> echo " My next birthday is over $daycount day's.";
> "
> Now I want to transform this into an UDF, to be able to use it on a comput
ed
> field.
>
> "Uri Dimant" wrote:
>|||On Wed, 8 Jun 2005 03:46:02 -0700, "Mrcio Santos" <Mrcio
Santos@.discussions.microsoft.com> wrote:

>Hi,
>I'm building an aplication in with I need to calculate the number of days
>till the persons next birthday. I already have an UDF that calculates the a
ge
>given the birthdate and the current date, and I would like to be able to do
>an UDF that calculates the number of days till next birthday to use it on a
>computed field.
>
Hi Mrcio,
It doesn't look pretty and I have the feeling that there should be an
easier way, but this code will at least get the job done:
DECLARE @.BirthDay smalldatetime
SET @.BirthDay = 'yyyymmdd' -- Insert test data here
SELECT DATEDIFF(day,
CURRENT_TIMESTAMP,
DATEADD(year,
DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
+ CASE WHEN DATEADD(year, DATEDIFF(year,
@.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
ELSE 0 END,
@.BirthDay))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx... it works perfectly...
"Hugo Kornelis" wrote:

> On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
> Santos@.discussions.microsoft.com> wrote:
>
> Hi Márcio,
> It doesn't look pretty and I have the feeling that there should be an
> easier way, but this code will at least get the job done:
> DECLARE @.BirthDay smalldatetime
> SET @.BirthDay = 'yyyymmdd' -- Insert test data here
> SELECT DATEDIFF(day,
> CURRENT_TIMESTAMP,
> DATEADD(year,
> DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
> + CASE WHEN DATEADD(year, DATEDIFF(year,
> @.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
> ELSE 0 END,
> @.BirthDay))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Calculate days till next birthday

Hi,
I'm building an aplication in with I need to calculate the number of days
till the persons next birthday. I already have an UDF that calculates the age
given the birthdate and the current date, and I would like to be able to do
an UDF that calculates the number of days till next birthday to use it on a
computed field.Look at DATEDIFF system funtion in the BOL
"M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> Hi,
> I'm building an aplication in with I need to calculate the number of days
> till the persons next birthday. I already have an UDF that calculates the
age
> given the birthdate and the current date, and I would like to be able to
do
> an UDF that calculates the number of days till next birthday to use it on
a
> computed field.
>|||I have, i've used it to build the other UDF I reffered. But this one isn't
thar simple. I have a PHP Script that solves this problem, it's something
like this:
"
$nextbirthday =mktime(0,0,0,"09","12",date("Y"));
// if $nextbirtday is in the past, add 1 year to $nextbirthday
if ($nextbirthday<$nowdate) {
$nextbirthday=$nextbirthday+(60*60*24*365);
}
// calculate the remaining day's till my next birthday
$daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
echo " My next birthday is over $daycount day's.";
"
Now I want to transform this into an UDF, to be able to use it on a computed
field.
"Uri Dimant" wrote:
> Look at DATEDIFF system funtion in the BOL
>
> "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> > Hi,
> > I'm building an aplication in with I need to calculate the number of days
> > till the persons next birthday. I already have an UDF that calculates the
> age
> > given the birthdate and the current date, and I would like to be able to
> do
> > an UDF that calculates the number of days till next birthday to use it on
> a
> > computed field.
> >
> >
>
>|||Hi
Did u try it this way:
DATEDIFF(d, getdate(), birthdate)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Márcio Santos" wrote:
> I have, i've used it to build the other UDF I reffered. But this one isn't
> thar simple. I have a PHP Script that solves this problem, it's something
> like this:
> "
> $nextbirthday => mktime(0,0,0,"09","12",date("Y"));
> // if $nextbirtday is in the past, add 1 year to $nextbirthday
> if ($nextbirthday<$nowdate) {
> $nextbirthday=$nextbirthday+(60*60*24*365);
> }
> // calculate the remaining day's till my next birthday
> $daycount=intval(($nextbirthday-$nowdate)/(60*60*24));
> echo " My next birthday is over $daycount day's.";
> "
> Now I want to transform this into an UDF, to be able to use it on a computed
> field.
>
> "Uri Dimant" wrote:
> > Look at DATEDIFF system funtion in the BOL
> >
> >
> > "M?rcio Santos" <Mrcio Santos@.discussions.microsoft.com> wrote in message
> > news:F540944C-F830-44E1-AADB-A90F7E018E89@.microsoft.com...
> > > Hi,
> > > I'm building an aplication in with I need to calculate the number of days
> > > till the persons next birthday. I already have an UDF that calculates the
> > age
> > > given the birthdate and the current date, and I would like to be able to
> > do
> > > an UDF that calculates the number of days till next birthday to use it on
> > a
> > > computed field.
> > >
> > >
> >
> >
> >|||On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
Santos@.discussions.microsoft.com> wrote:
>Hi,
>I'm building an aplication in with I need to calculate the number of days
>till the persons next birthday. I already have an UDF that calculates the age
>given the birthdate and the current date, and I would like to be able to do
>an UDF that calculates the number of days till next birthday to use it on a
>computed field.
>
Hi Márcio,
It doesn't look pretty and I have the feeling that there should be an
easier way, but this code will at least get the job done:
DECLARE @.BirthDay smalldatetime
SET @.BirthDay = 'yyyymmdd' -- Insert test data here
SELECT DATEDIFF(day,
CURRENT_TIMESTAMP,
DATEADD(year,
DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
+ CASE WHEN DATEADD(year, DATEDIFF(year,
@.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
ELSE 0 END,
@.BirthDay))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thx... it works perfectly...
"Hugo Kornelis" wrote:
> On Wed, 8 Jun 2005 03:46:02 -0700, "Márcio Santos" <Márcio
> Santos@.discussions.microsoft.com> wrote:
> >Hi,
> >I'm building an aplication in with I need to calculate the number of days
> >till the persons next birthday. I already have an UDF that calculates the age
> >given the birthdate and the current date, and I would like to be able to do
> >an UDF that calculates the number of days till next birthday to use it on a
> >computed field.
> >
> Hi Márcio,
> It doesn't look pretty and I have the feeling that there should be an
> easier way, but this code will at least get the job done:
> DECLARE @.BirthDay smalldatetime
> SET @.BirthDay = 'yyyymmdd' -- Insert test data here
> SELECT DATEDIFF(day,
> CURRENT_TIMESTAMP,
> DATEADD(year,
> DATEDIFF(year, @.BirthDay, CURRENT_TIMESTAMP)
> + CASE WHEN DATEADD(year, DATEDIFF(year,
> @.BirthDay, CURRENT_TIMESTAMP), @.BirthDay) < CURRENT_TIMESTAMP THEN 1
> ELSE 0 END,
> @.BirthDay))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Calculate Age

Hi,
I have a 'date of birth' of a person and a date of an event and need to
calculate the persons age at that event (both datetime format). Is there a
function available I can use for this ?
Any advice appreciated
Nichttp://www.aspfaq.com/2233
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eZ2TS%23tkGHA.1936@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a 'date of birth' of a person and a date of an event and need to
> calculate the persons age at that event (both datetime format). Is there a
> function available I can use for this ?
> Any advice appreciated
> Nic
>|||datediff(yy,dob,eventdate)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This approach is not always good one
select datediff(yy,'20061231','20070101')
--
Should be
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN MONTH('20061231') > MONTH('20070101')
OR MONTH('20061231') = MONTH('20070101')
AND DAY('20061231') > DAY('20070101') THEN 1
ELSE 0
END
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:9F8F5152-FBA9-4BDF-B358-01C9AEDB70C4@.microsoft.com...
> datediff(yy,dob,eventdate)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Thanks for poitning that out Uri.. looks like you are out to take me down
today :)
Juz joking.. u made me understand a few things.. usually I look at the holes
in the solution.. Wonder how I missed the crater :)
Anyways.. guess this should do..
SELECT DATEDIFF(year, '20061231', '20070101')
- CASE
WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
ELSE 0
END
Juz Rephrasing ur solution :)
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||That doesn't quite work because leap years have one more day than normal
years. So
SELECT DATEDIFF(year, '20071231', '20081231')
- CASE
WHEN datepart(dy,'20071231') > datepart(dy, '20081231') THEN 1
ELSE 0
END
correctly gives age as 1, but
SELECT DATEDIFF(year, '20081231', '20091231')
- CASE
WHEN datepart(dy,'20081231') > datepart(dy, '20091231') THEN 1
ELSE 0
END
gives age as 0.
Tom
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:BA42D051-F0AF-4971-8B42-6B83FE2757CC@.microsoft.com...
> Thanks for poitning that out Uri.. looks like you are out to take me down
> today :)
> Juz joking.. u made me understand a few things.. usually I look at the
> holes
> in the solution.. Wonder how I missed the crater :)
> Anyways.. guess this should do..
>
> SELECT DATEDIFF(year, '20061231', '20070101')
> - CASE
> WHEN datepart(dy,'20061231') > datepart(dy, '20070101') THEN 1
> ELSE 0
> END
> Juz Rephrasing ur solution :)
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Absolute murder.. better to keep my mouth shut for a while :)
So what happens if that guy was born on feb 29th?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Haven't you ever seen any Gilbert and Sullivan?
People born on Feb 29th are only about one fourth the age that we might
think they are.
In Pirates of Penzance, which my son just performed in as the Modern Major
General, a young man was indentured to the pirates until his 21st birthday.
But it turned out he was born Feb 29, so the 21st year after he had been
born, he had only had 5 birthdays, so his servitude had to continue.
HTH
Kalen Delaney, SQL Server MVP
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:53EC76A6-9297-4057-ACD4-6FD499DEC5EE@.microsoft.com...
> Absolute murder.. better to keep my mouth shut for a while :)
> So what happens if that guy was born on feb 29th?
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>|||Tracy,
As you probably know, a non-leap year has 365 days, and a leap year has 366
days:
Following returns 365:
SELECT DATEDIFF(day, '20030101', '20040101')
Following returns 366:
SELECT DATEDIFF(day, '20040101', '20050101')
Your expression would incorrectly return 1 for the following:
SELECT DATEDIFF(day, '20040102', '20050101') / 365
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:%23kwfIJxkGHA.4200@.TK2MSFTNGP05.phx.gbl...
> Niclas wrote:
> I *think* this should work, I can't seem to think of a case where it
> wouldn't:
> SELECT DATEDIFF(day, birthdate, eventdate) / 365
>|||Itzik Ben-Gan wrote:
> Tracy,
> As you probably know, a non-leap year has 365 days, and a leap year has 36
6
> days:
> Following returns 365:
> SELECT DATEDIFF(day, '20030101', '20040101')
> Following returns 366:
> SELECT DATEDIFF(day, '20040101', '20050101')
> Your expression would incorrectly return 1 for the following:
> SELECT DATEDIFF(day, '20040102', '20050101') / 365
>
Ahh, there's the case I didn't think of...