Subtract SQL Date

Question

In SQL, I would like to list all the funds whose anniversary is due this year in 2 months. What is the syntax?

Answer 1

SELECT *
FROM dbo.Funds
WHERE AnniversaryDate <= DATEADD(MONTH, 2, GETDATE())

This should work in SQL Server 2000 and later.

Mark

Answer 2

SELECT DATE_ADD(CURDATE(), INTERVAL 2 MONTH);

This will be done in MySQL. I did not add the anniversary comparison, because I do not know the structure of your tables.

Answer 3

, , , , - .

SELECT *
FROM funds
WHERE CURRENT_DATE <= anniversary
AND CURRENT_DATE > DATE_SUB(anniversary, INTERVAL 2 MONTH)

, , , SQL .

Answer 4

, " " , , - , , 1972-01-03.

, , .

In SQL Serveryou need to generate a list of years and join it:

WITH    q AS
        (
        SELECT  0 AS num
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num <= 100
        )
SELECT  *
FROM    mytable
WHERE   fund_date BETWEEN DATE_ADD(year, -num, GETDATE()) AND DATE_ADD(year, -num, DATE_ADD(month, 2, GETDATE()))
UNION ALL
SELECT  *
FROM    mytable
WHERE   fund_date <= DATEADD(year, -100, GETDATE()
        AND DATE_ADD(year, YEAR(GETDATE()) - YEAR(fund_date), fund_date) BETWEEN GETDATE() AND DATE_ADD(month, 2, GETDATE()))

This request consists of two parts:

• The first part selects birthdays for each year from the list by scanning the index range.
• The second part selects birthdays that are more than 100years old (using a single-range scan) and filters them all.

Since birth dates older than 100years are very different, the second part actually selects almost nothing and does not affect performance too much.