FromDate&在14天后改变日期。 [英] FromDate & ToDate to be Changed after 14 Days.
问题描述
我有一张名为Mst_Crew的桌子。
字段是
Cr_No numeric
Cr_Name varchar
FromDate date
ToDate date
我需要更改FromDate& ToDate ToDate传递当前日期然后需要在FromDate上添加14天
例如
CrNo CrewName FromDate ToDate
***** ********************************************
1 A. 29-Nov-15 13-Dec-15
2 B 06-Dec-15 20-Dec-15
3 C 13-Dec-15 27-Dec-15
4 D 22- Nov-15 06-Dec-15
下一个更改将是
CrNo CrewName FromDate ToDate
**********************************************
1 A 27-Dec-15 10-Jan-16
2 B 03-Jan-16 17-Jan-16
3 C 10-Jan-16 24-Jan-16
4 D 20-Dec-15 03-Jan-16
这意味着当Todate< GetDate()然后在FromDate Column ToDate + 14和ToDate Column FromDate + 14
如何在SQL中执行此操作?
试试这个
DECLARE @ checkDate 日期 = dateadd(dd,datediff(dd, 0 ,getdate ()), 0 ) - 即今天的开始
update Mst_Crew
set FromDate = DATEADD (dd, 14 ,ToDate),ToDate = DATEADD(dd, 2 * 14 ,ToDate)
其中 ToDate< @ checkDate正如我在评论中提到的,这在2015年12月17日运行时给出了(正确的)以下结果
1 A 2015-12-27 2016-01-10
2 B 2015-12-06 2015-12-20
3 C 2015-12- 13 2015-12-27
4 D 2015-12-20 2016-01-03如果我将日期提前14天
SET @checkdate = dateadd(dd, 14 ,@ checkdate)
我得到
1 A 2015-12的预期结果-27 2016-01-10
2 B 2016-01-03 2016-01-17
3 C 2016-01-10 2016-01-24
4 D 2015-12-20 2016年1月3日
I have table called Mst_Crew.
Fields are
Cr_No numeric
Cr_Name varchar
FromDate date
ToDate date
I need to change the FromDate & ToDate when the ToDate pass the currentdate then need to be add 14Days on FromDate
For Example
CrNo CrewName FromDate ToDate
*************************************************
1 A 29-Nov-15 13-Dec-15
2 B 06-Dec-15 20-Dec-15
3 C 13-Dec-15 27-Dec-15
4 D 22-Nov-15 06-Dec-15
The Next Change will be
CrNo CrewName FromDate ToDate
**********************************************
1 A 27-Dec-15 10-Jan-16
2 B 03-Jan-16 17-Jan-16
3 C 10-Jan-16 24-Jan-16
4 D 20-Dec-15 03-Jan-16
It means that when Todate<GetDate() then in FromDate Column ToDate+14 and in ToDate Column FromDate+14
How to do this in SQL?
Try this
DECLARE @checkDate Date = dateadd(dd, datediff(dd, 0,getdate()), 0) -- i.e. BEGINNING of today update Mst_Crew set FromDate = DATEADD(dd, 14, ToDate), ToDate = DATEADD(dd, 2 * 14, ToDate) where ToDate < @checkDateAs I mentioned in my comment this gives (correct) results of the following when run on 17-Dec-2015
1 A 2015-12-27 2016-01-10 2 B 2015-12-06 2015-12-20 3 C 2015-12-13 2015-12-27 4 D 2015-12-20 2016-01-03If I then move the date on by 14 days
SET @checkdate = dateadd(dd, 14, @checkdate)
I get your expected results of
1 A 2015-12-27 2016-01-10 2 B 2016-01-03 2016-01-17 3 C 2016-01-10 2016-01-24 4 D 2015-12-20 2016-01-03
这篇关于FromDate&在14天后改变日期。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!