sql - Update a date field in selected records with a calculated, relative date - Stack Overflow

admin管理员组

文章数量:1391934

The end result I am looking for is to have a query that will find specific records in Table1, use a field in joined Table2 to retrieve a day of the week, use a formula to determine the next occurring calendar date of that day of week and update date fields in Talbe1 with that calendar date.

Taken individually, the pieces are not that complicated. However putting the pieces together is proving to be a challenge.

If someone can give me the structure of the query I would greatly appreciate it. By "structure" I mean the order of DECLARE, SELECT, FROM, UPDATE, etc. statements.

Here are the pieces that I have:

DECLARE Var1 INT -- This is the day of week from Field5 in Table2
    Var2 DATE  -- This is the next calendar date to use in the update

SET Var2 = [calculation for next calendar date]

UPDATE Table1.
 SET Field6 = Var2
     ,Field7 = Var2
     ,Field8 = Var2

FROM Table1
   INNER JOIN Table2 on Table1.Field1 = Table2.Field1 and
          Table1.Field2 = Table2.Field2

WHERE Table1.Field3 IS NULL and Table2.Field4 is 'VALUE'

The end result I am looking for is to have a query that will find specific records in Table1, use a field in joined Table2 to retrieve a day of the week, use a formula to determine the next occurring calendar date of that day of week and update date fields in Talbe1 with that calendar date.

Taken individually, the pieces are not that complicated. However putting the pieces together is proving to be a challenge.

If someone can give me the structure of the query I would greatly appreciate it. By "structure" I mean the order of DECLARE, SELECT, FROM, UPDATE, etc. statements.

Here are the pieces that I have:

DECLARE Var1 INT -- This is the day of week from Field5 in Table2
    Var2 DATE  -- This is the next calendar date to use in the update

SET Var2 = [calculation for next calendar date]

UPDATE Table1.
 SET Field6 = Var2
     ,Field7 = Var2
     ,Field8 = Var2

FROM Table1
   INNER JOIN Table2 on Table1.Field1 = Table2.Field1 and
          Table1.Field2 = Table2.Field2

WHERE Table1.Field3 IS NULL and Table2.Field4 is 'VALUE'

• sql
• sql-server
• date
• sql-update
• calculated-field

Share Improve this question Follow edited Mar 13 at 3:17 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Mar 12 at 15:20 Mark SimmondsMark Simmonds 2133 bronze badges 1

• Something like: UPDATE Table1. SET Field6 = DATEADD(DAY, 1, Table2 .Field5) ,Field7 = DATEADD(DAY, 1, Table2 .Field5) ,Field8 = DATEADD(DAY, 1, Table2 .Field5) FROM Table1 INNER JOIN Table2 on Table1.Field1 = Table2.Field1 and Table1.Field2 = Table2.Field2 WHERE Table1.Field3 IS NULL and Table2.Field4 is 'VALUE' ? DATEADD(DAY, 1, Field) adds a day to the field. If you don't want time but only dates, you can wrap it into a DATE cast. If you want to handle weekends, you can probably find answers for how to calculate next working day – siggemannen Commented Mar 12 at 15:28

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Following might help:

-- Declare variables
DECLARE @Var1 INT; 
DECLARE @Var2 DATE;

-- Set the day of the week from Table2
SELECT @Var1 = t2.Field5
FROM Table1 t1
INNER JOIN Table2 t2 
    ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2
WHERE t1.Field3 IS NULL AND t2.Field4 = 'VALUE';

-- Calculate the next occurring calendar date for the given day of the week
SET @Var2 = DATEADD(DAY, (7 - DATEPART(WEEKDAY, GETDATE()) + @Var1) % 7, GETDATE());

-- Update Table1 with the calculated date
UPDATE t1
SET Field6 = @Var2,
    Field7 = @Var2,
    Field8 = @Var2
FROM Table1 t1
INNER JOIN Table2 t2 
    ON t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2
WHERE t1.Field3 IS NULL AND t2.Field4 = 'VALUE';

本文标签： sqlUpdate a date field in selected records with a calculatedrelative dateStack Overflow