SQL查询需求：同表天数偏移日期及参数日期区间判断

Got it, let's break down your two SQL questions with practical examples that work across common database systems.

Assuming your table (let's call it event_log for example) has two columns:

• event_date: The base date you want to modify
• days_offset: An integer representing the number of days to add to event_date

Here's how to write the query for major databases:

MySQL/MariaDB #

Use the DATE_ADD() function to add the days:

SELECT 
  event_date,
  days_offset,
  DATE_ADD(event_date, INTERVAL days_offset DAY) AS adjusted_date
FROM event_log;

PostgreSQL #

PostgreSQL lets you directly add an interval constructed from the integer column:

SELECT 
  event_date,
  days_offset,
  event_date + (days_offset || ' days')::INTERVAL AS adjusted_date
FROM event_log;
-- Or a cleaner alternative using make_interval:
SELECT 
  event_date,
  days_offset,
  event_date + make_interval(days => days_offset) AS adjusted_date
FROM event_log;

SQL Server #

Use the DATEADD() function, specifying day as the date part:

SELECT 
  event_date,
  days_offset,
  DATEADD(day, days_offset, event_date) AS adjusted_date
FROM event_log;

You need to pass an input date parameter and check if it lies between 2018-03-03 and 2018-03-08 (inclusive, since you mentioned the interval is 5 days). Note that if your parameter includes a time component, you might need to adjust the end date to capture the entire day.

Example Query (Works with Minor Adjustments Across Databases) #

First, here's a generic version that returns a clear yes/no result:

MySQL/MariaDB

Using a user-defined parameter (@input_date):

SET @input_date = '2018-03-05'; -- Your input date here
SELECT 
  CASE 
    WHEN @input_date BETWEEN '2018-03-03' AND '2018-03-08' 
    THEN 'Yes, date is in range' 
    ELSE 'No, date is outside range' 
  END AS date_range_check;

If your input has a time component (e.g., 2018-03-08 14:30:00), use < '2018-03-09' instead of <= '2018-03-08' to include the entire day:

SET @input_date = '2018-03-08 14:30:00';
SELECT 
  CASE 
    WHEN @input_date >= '2018-03-03' AND @input_date < '2018-03-09' 
    THEN 'Yes, date is in range' 
    ELSE 'No, date is outside range' 
  END AS date_range_check;

PostgreSQL

Using a positional parameter ($1):

SELECT 
  CASE 
    WHEN $1 BETWEEN '2018-03-03'::DATE AND '2018-03-08'::DATE 
    THEN 'Yes, date is in range' 
    ELSE 'No, date is outside range' 
  END AS date_range_check;
-- When executing, pass your date as the first parameter (e.g., '2018-03-05')

SQL Server

Using a declared parameter (@input_date):

DECLARE @input_date DATE = '2018-03-05';
SELECT 
  CASE 
    WHEN @input_date BETWEEN '2018-03-03' AND '2018-03-08' 
    THEN 'Yes, date is in range' 
    ELSE 'No, date is outside range' 
  END AS date_range_check;

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