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 using multiple fields

The database I work with uses a BirthDay, BirthMonth, and BirthYear field to hold data concerning an individual. I need to calculate their age - down to the day, if the individual is only days old, or month if the individual is months old, etc. I found a formula that got it to years, but no further.

Thanks for your help!

you can try this

DECLARE @.Person TABLE (
BirthMonth int
, BirthDate int
, BirthYear int
, BirthDay AS CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear)
, Age AS CASE WHEN DATEDIFF(year,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()) > 0 THEN
CONVERT(varchar,DATEDIFF(year,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE())) + ' year(s) old'
WHEN DATEDIFF(month,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()) = 0 THEN
CONVERT(varchar,ABS(DATEDIFF(day,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()))) + ' day(s) old'
ELSE
CONVERT(varchar,ABS(DATEDIFF(month,CONVERT(varchar(2),BirthMonth) + '/' + CONVERT(varchar(2),BirthDate) + '/' + CONVERT(varchar(4),BirthYear),GETDATE()))) + ' month(s) old'
END
)

INSERT
INTO @.Person
SELECT 10,21,2005

INSERT
INTO @.Person
SELECT 4,28,2007

INSERT
INTO @.Person
SELECT 5,20,2007

SELECT *
FROM @.person|||It works! Thank you so much!