> Without giving this too much thought, it seems to me that you could either
> (a) put the expiryDate part before the startDate part, or (b) just create
a
> local variable within your stored procedure that has the value you want.
But
> maybe I'm missing something.
That would result in
Before update
startDate expiryDate
23/03/2003 17:00 AM 23/03/2003 17:10 AM
After update
23/03/2003 17:20 AM 23/03/2003 17:20 AM
While the result I want is
23/03/2003 17:10 AM 23/03/2003 17:20 AM
However your suggestion did help me.
1. UPDATE thisTable
3. expiryDate = DATEADD(minute, DATEDIFF(minute, startDate,
expiryDate)/2, GETDATE())
2. SET startDate = DATEADD(minute, DATEDIFF(minute, startDate,
expiryDate), GETDATE()),
4. WHERE (pkAuctionID IN (' + @lstAuctionID + ')) '
Will do the trick, notice the /2 on line 2..
Thanks
> > I have this Stored Procedure that basically relists an action
> > when it is finished
> >
> > 1. UPDATE thisTable
> > 2. SET startDate = DATEADD(minute, DATEDIFF(minute,
> > startDate, expiryDate), GETDATE()),
> > 3. expiryDate = DATEADD(minute, DATEDIFF(minute,
> > startDate, expiryDate), GETDATE())
> > 4. WHERE (pkAuctionID IN (' + @lstAuctionID + ')) '
> >
> > The problem comes in on line 2 and 3, obviously when the
> > Startdate (line 2) is updated with the new value then on line
> > 3 there is no difference in time anymore. The only way I
> > think I can overcome this issue is by using a CURSOR, but I
> > would like to do it without a CURSOR if possible.
> >
> > Example:
> > Before update
> > startDate expiryDate
> > 23/03/2003 17:00 AM 23/03/2003 17:10 AM
> > After update
> > 23/03/2003 17:10 AM 23/03/2003 17:10 AM
> > While the result I want is
> > 23/03/2003 17:10 AM 23/03/2003 17:20 AM
>
> Without giving this too much thought, it seems to me that you could either
> (a) put the expiryDate part before the startDate part, or (b) just create
a
> local variable within your stored procedure that has the value you want.
But
> maybe I'm missing something.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
> voice: (202) 797-5496
> fax: (202) 797-5444
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
