Date Calculations

Hi Everyone,
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 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
Please help...
ROOTry this: dateadd(w,-1,event_date)|||sorry abou the previous. Should be dateadd(ww,-1,event_date)|||Roplab wrote:
> Hi Everyone,
> 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 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
> Please help...
> ROO
It's a lot easier to help if you provide DDL and sample data in the form of
insert statements (www.aspfaq.com/5006). As it now stands, I have to guess
at the data type and name of the "Event Date" column. Here is my solution
based on the guess that it is a datetime column (you should look up Using
Date and Time Data in SQL Books Online). This is an example using variables.
You should be able to convert it to a select statement if it is relevant to
your situation:
declare @.eventdate datetime
set @.eventdate='20050114'
select DATEADD(ww,-1,@.eventdate)
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi Everyone,
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 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
--Wly Reminder
if @.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
GO
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OjAul7SDFHA.1264@.TK2MSFTNGP12.phx.gbl...
> Roplab wrote:
> It's a lot easier to help if you provide DDL and sample data in the form
of
> insert statements (www.aspfaq.com/5006). As it now stands, I have to guess
> at the data type and name of the "Event Date" column. Here is my solution
> based on the guess that it is a datetime column (you should look up Using
> Date and Time Data in SQL Books Online). This is an example using
variables.
> You should be able to convert it to a select statement if it is relevant
to
> your situation:
> declare @.eventdate datetime
> set @.eventdate='20050114'
> select DATEADD(ww,-1,@.eventdate)
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>