Link to home
Start Free TrialLog in
Avatar of MrBrownUK
MrBrownUK

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

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

Open in new window


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);

Open in new window


Could I get some help with my update syntax/statement as it will not execute correctly. Thank you
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

the IN needs to return the value being tested - and only 1 column when doing so.
try:
update    WEEK_PHASINGS
set       PHASING = 100
where     PROMO_ID_REF in (
          select    WEEK_P.PROMO_ID_REF
          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);

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of santhimurthyd
santhimurthyd
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>> datediff (wk, SH.SHIP_START_DT,SH.SHIP_END_DT ) <<

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.