I have got a problem with date calculation. I have a procedure that all
me to insert date into a Table based on user input. The input is a Event
Date and Reminder
Example: if the user Enter an Event Date and choose to a reminder for a
certain event... I need to calculate a date that will be a w
prior to theevent date as the reminder
My question is how do I calculate prior w
of a certain Date.. e.g EventDate = 01/14/2005 I want the reminder to be calculate has
Reminder=01/07/2005
Below is my procedure:
CREATE PROCEDURE EventReminder
@.DocketID int,
@.EventName varchar(50),
@.Reminder int,
@.EventNumber int,
@.EventDate varchar(50)
AS
--Declare variables
Declare @.EventStartNum int,
@.EventReminderNum int,
@.EventDate1 datetime,
@.EventNum int
--Initialize the Variables
set @.EventStartNum = 0
set @.EventReminderNum = 0
set @.EventNum = -1
--Delete the Reminder if the DocketID already exist
delete from reminder where DocketID = @.DocketID
--Start the loop
while @.EventStartNum < @.EventNumber
Begin --Start Begin
set @.EventStartNum = @.EventStartNum + 1
--W
ly Reminderif @.EventNumber = 1
begin
while @.Reminder >
@.EventReminderNum
begin
--Increment of the w
set @.EventReminderNum =
@.EventReminderNum + 1
set @.EventDate1 = DATEADD(w
,@.EventReminderNum, @.EventDate)
insert into Reminder
(DocketID, EventDate, EventName, Reminder)
Values
(@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
set @.EventNum = @.EventNum - 1
end
end
--print 'The counter is ' +
convert(varchar(50),@.EventDate1,101)
end --End Begin
GOUse function DATEADD.
Example:
select dateadd(ww, -1, '20050114')
go
AMB
"Roplab" wrote:
> Hi Everyone,
> I have got a problem with date calculation. I have a procedure that al
l
> me to insert date into a Table based on user input. The input is a Event
> Date and Reminder
> Example: if the user Enter an Event Date and choose to a reminder for a
> certain event... I need to calculate a date that will be a w
prior to the
> event date as the reminder
> My question is how do I calculate prior w
of a certain Date.. e.g Event
> Date = 01/14/2005 I want the reminder to be calculate has
> Reminder=01/07/2005
> Below is my procedure:
> CREATE PROCEDURE EventReminder
> @.DocketID int,
> @.EventName varchar(50),
> @.Reminder int,
> @.EventNumber int,
> @.EventDate varchar(50)
> AS
> --Declare variables
> Declare @.EventStartNum int,
> @.EventReminderNum int,
> @.EventDate1 datetime,
> @.EventNum int
> --Initialize the Variables
> set @.EventStartNum = 0
> set @.EventReminderNum = 0
> set @.EventNum = -1
>
> --Delete the Reminder if the DocketID already exist
> delete from reminder where DocketID = @.DocketID
> --Start the loop
> while @.EventStartNum < @.EventNumber
> Begin --Start Begin
> set @.EventStartNum = @.EventStartNum + 1
> --W
ly Reminder> if @.EventNumber = 1
> begin
> while @.Reminder >
> @.EventReminderNum
> begin
> --Increment of the w
> set @.EventReminderNum =
> @.EventReminderNum + 1
> set @.EventDate1 = DATEADD(wee
k,
> @.EventReminderNum, @.EventDate)
> insert into Reminder
> (DocketID, EventDate, EventName, Reminder)
> Values
> (@.DocketID,convert(varchar(50),@.EventDat
e1,101), @.EventName, @.Reminder)
> set @.EventNum = @.EventNum -
1
> end
> end
> --print 'The counter is ' +
> convert(varchar(50),@.EventDate1,101)
> end --End Begin
> GO
>
>
