如何在MSSQL Server中按日期条件更新对应标志列

Got it, let's break this down clearly. The core goal is to set each flag column to 0 only if its corresponding date column is older than 8 weeks from today—otherwise, we leave the flag as its default NULL value. Below are tailored solutions for the most common databases:

1. SQL Server #

Use DATEADD to calculate the 8-week threshold, then use CASE statements to update flags selectively:

UPDATE YourTableName
SET
    FlagOfColumn1 = CASE WHEN Column1 < DATEADD(WEEK, -8, GETDATE()) THEN 0 ELSE FlagOfColumn1 END,
    FlagOfColumn2 = CASE WHEN Column2 < DATEADD(WEEK, -8, GETDATE()) THEN 0 ELSE FlagOfColumn2 END,
    FlagOfColumn3 = CASE WHEN Column3 < DATEADD(WEEK, -8, GETDATE()) THEN 0 ELSE FlagOfColumn3 END,
    FlagOfColumn4 = CASE WHEN Column4 < DATEADD(WEEK, -8, GETDATE()) THEN 0 ELSE FlagOfColumn4 END

• Quick note: GETDATE() returns the current datetime, while SYSDATETIME() offers higher precision—pick whichever matches your date column's type.

2. MySQL / MariaDB #

Leverage DATE_SUB with interval syntax and IF statements for concise updates:

UPDATE YourTableName
SET
    FlagOfColumn1 = IF(Column1 < DATE_SUB(CURDATE(), INTERVAL 8 WEEK), 0, FlagOfColumn1),
    FlagOfColumn2 = IF(Column2 < DATE_SUB(CURDATE(), INTERVAL 8 WEEK), 0, FlagOfColumn2),
    FlagOfColumn3 = IF(Column3 < DATE_SUB(CURDATE(), INTERVAL 8 WEEK), 0, FlagOfColumn3),
    FlagOfColumn4 = IF(Column4 < DATE_SUB(CURDATE(), INTERVAL 8 WEEK), 0, FlagOfColumn4)

• Use NOW() instead of CURDATE() if your date columns include time components and you need full datetime comparisons.

3. PostgreSQL #

PostgreSQL makes interval subtraction straightforward with CURRENT_DATE or NOW():

UPDATE YourTableName
SET
    FlagOfColumn1 = CASE WHEN Column1 < CURRENT_DATE - INTERVAL '8 weeks' THEN 0 ELSE FlagOfColumn1 END,
    FlagOfColumn2 = CASE WHEN Column2 < CURRENT_DATE - INTERVAL '8 weeks' THEN 0 ELSE FlagOfColumn2 END,
    FlagOfColumn3 = CASE WHEN Column3 < CURRENT_DATE - INTERVAL '8 weeks' THEN 0 ELSE FlagOfColumn3 END,
    FlagOfColumn4 = CASE WHEN Column4 < CURRENT_DATE - INTERVAL '8 weeks' THEN 0 ELSE FlagOfColumn4 END

• For timestamp columns, swap CURRENT_DATE with NOW() to account for time in your comparisons.

Important Reminders #

• Replace YourTableName with your actual table name.
• The CASE/IF logic ensures we only update flags that meet the date condition—all other flags stay NULL as required.
• If you need this rule to apply automatically (e.g., when new rows are added or dates are modified), look into creating a trigger for your specific database.

内容的提问来源于stack exchange，提问作者DevKriya