Birthdate

Question

Post reply

Birthdate

edward_hall76

SSC Eights!

Points: 882
More actions
September 13, 2013 at 9:31 pm

#298773

I have this stored proc USE [SMS]
GO
/****** Object: StoredProcedure [dbo].[usp_Birthday] Script Date: 09/13/2013 22:26:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_Birthday]
as
begin
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--catch block
--block here if needed
begin try
begin Transaction;
DECLARE @TodaysDate date = getdate(),
@PhoneNumber nvarchar(max),
@FirstName varchar(25) ,
@body nvarchar(MAX),-- = ' Happy Birthday '
@txtattnet varchar(25),
@Date datetime
DECLARE @CurrentMonth int = MONTH(GETDATE()), @CurrentDay int = DAY(GETDATE())
DECLARE @Birthdate TABLE(ID int IDENTITY(1,1), PhoneNumber nvarchar(MAX), FirstName varchar(25))
------check phone carriers ---------
--set Identity_insert [@Birthday] on
INSERT @Birthdate
SELECT PhoneNumber, FirstName from dbo.Name where MONTH(BirthDate) = @Date--@CurrentMonth and DAY(BirthDate) = @CurrentDay
DECLARE @NumberOfBirthdays smallint = (SELECT COUNT(*) from @Birthdate)
DECLARE @MinID int
WHILE @NumberOfBirthdays > 0
BEGIN
SET @MinID = (SELECT MIN(ID) From @Birthdate)
SET @PhoneNumber = (SELECT PhoneNumber from @Birthdate where ID = @MinID)
set @FirstName = (Select FirstName from @Birthdate where ID = @MinID)
SET @body = 'Happy BirthDay' + ', ' + @FirstName
EXECUTE msdb.dbo.sp_send_dbmail
@subject = 'Happy Birthday',
@recipients = @PhoneNumber,
--@blind_copy_recipients = ***@*****.com; ***@*****.com',
@body = @body,
@profile_name ='gmail';
DELETE FROM @Birthdate where ID = @MINID
SET @NumberOfBirthdays = @NumberofBirthdays -1
END
That part work good and it send out the birth day wish to them but I want it to add there age to the message and for some reason I can not figure out how to get that to work.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

datediff(YEAR, BirthDate, getdate())

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You do realize that your code will only 25% of the time for individuals born on 2/29 of a leap year, right?

edward_hall76 SSC Eights! Points: 882 More actions · Answer 3

edward_hall76

SSC Eights!

Points: 882

September 14, 2013 at 8:17 pm

#1650256

did not notice that.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

Erland Sommarskog (9/14/2013)
datediff(YEAR, BirthDate, getdate())

Doesn't work, Erland. Consider a baby born on 12/31 of some year and using your code the next day.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

Jeff Moden (9/15/2013)
Doesn't work, Erland. Consider a baby born on 12/31 of some year and using your code the next day.

In that case, there is some other bug. The proposition was that we had already selected people born on this day. That baby should not get a mail until 364 days later.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 6

Erland Sommarskog (9/15/2013)
Jeff Moden (9/15/2013)
Doesn't work, Erland. Consider a baby born on 12/31 of some year and using your code the next day.
In that case, there is some other bug. The proposition was that we had already selected people born on this day. That baby should not get a mail until 364 days later.

Ah... got it. No complex age calculation required if you send it out on the birthday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)