How can i find a the date of birthday ?

Question

Post reply

How can i find a the date of birthday ?

midan1

SSCertifiable

Points: 5100
More actions
July 17, 2003 at 3:02 pm

#161834

how can i do This ??
i have a table thet contain the details of
employee .
like this
table name "users"
fields names
----------------
FirstName = bob
LastName = miler
id=6666666666666
Date_born =17/07/1968
--------------------------
1
how can i find the date of birthday evry year
for all the employee
--------------------------------------
2
how can i send to the employee a
email in morning 07:00 AM
like this !
" happy birthday "
"bob miler"
" your age is 35"
use SQL Mail
--------------------------
thnks ilan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

This will give you the age and the people born on that day.

select

[id],

FirstName,

LastName,

Date_born,

DATEDIFF(yyyy,Date_born,GETDATE()) AS Age

from dbo.users

WHERE

DATEADD(yyyy,-(YEAR(Date_born)-1900),Date_born) = DATEADD(yyyy,-(YEAR(GETDATE())-1900),GETDATE())

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hello Ilan,

quote:
how can i do This ??
i have a table thet contain the details of
employee .
like this
table name "users"
fields names
----------------
FirstName = bob
LastName = miler
id=6666666666666
Date_born =17/07/1968
--------------------------
1
how can i find the date of birthday evry year
for all the employee
--------------------------------------
2
how can i send to the employee a
email in morning 07:00 AM
like this !
" happy birthday "
"bob miler"
" your age is 35"
use SQL Mail
--------------------------
thnks ilan

that's a nice feature you're implementing!

Hopefully it comes along with an automated extra transfer to the banking account of the person in question

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Oh, another thought. What day will you use for people who were born leap-day. I didn't account for that.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

quote:
Oh, another thought. What day will you use for people who were born leap-day. I didn't account for that.

in this case you're already punished enough

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

midan1 SSCertifiable Points: 5100 More actions · Answer 5

so no help for my

seriously

------------------

question

how can i know how has a birthday

from table "users"

this week

---------------------

thnks

ilan

David Burrows SSC Guru Points: 65142 More actions · Answer 6

 SET DATEFIRST 1 --Monday

declare @startday int,@endday int

set @startday = day(getdate()-datepart(weekday,getdate())+1)

set @endday = @startday+6

select FirstName,LastName

from users

where month(Date_born) = month(getdate())

and day(Date_born) >= @startday

and day(Date_born) <= @endday

Far away is close at hand in the images of elsewhere.
Anon.

midan1 SSCertifiable Points: 5100 More actions · Answer 7

thnks

-------

how can i put it all in one VIEW

so thet i can see all in one table

not in Stored Procedures

---------

thnks a lot

ilan

David Burrows SSC Guru Points: 65142 More actions · Answer 8

This view will return values from the table plus two extra columns for BirthdayToday and BirthdayThisWeek

DROP VIEW UserBirthDay 

GO 

CREATE VIEW UserBirthDay AS 

SELECT [id],FirstName,LastName,Date_born, 

DATEDIFF(yyyy,Date_born,GETDATE()) AS Age, 

(CASE WHEN DATEADD(yyyy,YEAR(GETDATE())-YEAR(Date_born),Date_born) = GETDATE() THEN 1 ELSE 0 END) AS BirthdayToday, 

(CASE WHEN DATEADD(yyyy,YEAR(GETDATE())-YEAR(Date_born),Date_born) BETWEEN (GETDATE() - DATEPART(WEEKDAY,GETDATE()) + 1) AND (GETDATE() - DATEPART(WEEKDAY,GETDATE()) + 1 + 6) THEN 1 ELSE 0 END) AS BirthdayThisWeek 

FROM dbo.users  

GO

As for leap years, SQL converts 2000-02-29 plus 3 years as 2003-02-28. Depending on whether you want this for Feb 29 or you add 1 day (with a case statement) to get Mar 1.

Edited by - davidburrows on 07/18/2003 07:17:57 AM

Far away is close at hand in the images of elsewhere.
Anon.

midan1 SSCertifiable Points: 5100 More actions · Answer 9

wow thnks

-------------

why BirthdayToday not work ??

only BirthdayThisWeek

---------

thnks A LOT

ilan

Edited by - midan1 on 07/20/2003 12:35:08 PM

Edited by - midan1 on 07/21/2003 08:59:42 AM