asked on
SQL Update with DATEDIFF function
I have written the following SQL to retrieve all records in WEEK_PHASINGS where the shipment period is less than one week.
I then want to update the column PHASINGS in table WEEK_PHASINGS where this is true to 100
I then want to update the column PHASINGS in table WEEK_PHASINGS where this is true to 100
Could I get some help with my update syntax/statement as it will not execute correctly. Thank you
I then want to update the column PHASINGS in table WEEK_PHASINGS where this is true to 100
select WEEK_P.SKU_RATIO_ID,
SH.SHIP_START_DT,
SH.SHIP_END_DT,
datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT )AS DATEDIFF_SHIP_ST_END
from SHIP_HIST SH
inner join WEEK_PHASINGS WEEK_P
on WEEK_P.PROMO_ID_REF = SH.PROMO_ID
where datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT ) = 0
I then want to update the column PHASINGS in table WEEK_PHASINGS where this is true to 100
update WEEK_PHASINGS
set PHASING = 100
where PROMO_ID_REF in (
select WEEK_P.SKU_RATIO_ID,
SH.SHIP_START_DT,
SH.SHIP_END_DT,
datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT )AS DATEDIFF_SHIP_ST_END
from SHIP_HIST SH
inner join WEEK_PHASINGS WEEK_P
on WEEK_P.PROMO_ID_REF = SH.PROMO_ID
where datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT ) = 0);
Could I get some help with my update syntax/statement as it will not execute correctly. Thank you
Microsoft SQL Server 2005Microsoft SQL Server 2008Microsoft SQL Server
Last Comment
Scott Pletcher
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
>> datediff (wk, SH.SHIP_START_DT,SH.SHIP_E
What *specifically* are you trying to test on the dates?
Be aware that SQL Server's DATEDIFF checks "time boundaries" crossed, NOT a specific elapsed time.
For example, midnight is the boundary for a day -- of course -- so this:
SELECT DATEDIFF(DAY, '20120702 23:59:59.997', '20120703 00:00')
shows "one day", even though only 3 ms have elapsed.
Likewise, "DATEDIFF(WEEK," will give the count of week boundaries passed, which seems to be based on the @@DATEFIRST setting.
What *specifically* are you trying to test on the dates?
Be aware that SQL Server's DATEDIFF checks "time boundaries" crossed, NOT a specific elapsed time.
For example, midnight is the boundary for a day -- of course -- so this:
SELECT DATEDIFF(DAY, '20120702 23:59:59.997', '20120703 00:00')
shows "one day", even though only 3 ms have elapsed.
Likewise, "DATEDIFF(WEEK," will give the count of week boundaries passed, which seems to be based on the @@DATEFIRST setting.
try:
Open in new window