如何在不指定年份时从数据库查询10月25-31日的目标数据

Hey there! Let's get your SQL query sorted to pull all those late-October horror movie dates, regardless of the year.

The main issue with your current query is that it only targets a single date, and using LIKE for date matching can be fragile if your date format ever changes. Instead, using database-specific date functions to extract the month and day is way more reliable. Here are solutions for the most common SQL dialects:

SQL Server #

Use DATEPART() to grab the month and day from your Date column:

SELECT [ID] 
FROM dates 
WHERE [ID] IN (
    SELECT [ID] 
    FROM movies 
    WHERE Genre = 'Horror' 
      AND DATEPART(MONTH, [Date]) = 10 
      AND DATEPART(DAY, [Date]) BETWEEN 25 AND 31
);

MySQL/MariaDB #

Use MONTH() and DAY() functions for simpler syntax:

SELECT ID 
FROM dates 
WHERE ID IN (
    SELECT ID 
    FROM movies 
    WHERE Genre = 'Horror' 
      AND MONTH(Date) = 10 
      AND DAY(Date) BETWEEN 25 AND 31
);

PostgreSQL #

You can use EXTRACT() or TO_CHAR() for this:

-- Using EXTRACT
SELECT ID 
FROM dates 
WHERE ID IN (
    SELECT ID 
    FROM movies 
    WHERE Genre = 'Horror' 
      AND EXTRACT(MONTH FROM "Date") = 10 
      AND EXTRACT(DAY FROM "Date") BETWEEN 25 AND 31
);

-- Or using TO_CHAR for a more readable match
SELECT ID 
FROM dates 
WHERE ID IN (
    SELECT ID 
    FROM movies 
    WHERE Genre = 'Horror' 
      AND TO_CHAR("Date", 'MM-DD') BETWEEN '10-25' AND '10-31'
);

If your Date column is stored as a string (not recommended!) #

If you can't convert the column to a proper date type, you can adjust the LIKE pattern to cover the range:

SELECT [ID] 
FROM dates 
WHERE [ID] IN (
    SELECT [ID] 
    FROM movies 
    WHERE Genre = 'Horror' 
      AND ([Date] LIKE '%-10-2[5-9]' OR [Date] LIKE '%-10-3[0-1]')
);

This matches dates ending with -10-25 to -10-29 and -10-30 to -10-31. But again, using proper date functions is always better to avoid edge cases (like inconsistent date formatting).

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