CODEWITHSUNDEEP
sql
Zitto
Zitto

Reputation:

SQL date subtraction

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

Upvotes: 3

Views: 691

Answers (4)

Quassnoi
Quassnoi

Reputation: 425291

I don't know if "fund anniversary" is some kind of a special term in English, so I'm assuming you want to select something like the birthdays which can be stored with a random year, like 1972-01-03.

If it's not so, please correct me.

In SQL Server, you need to generate a list of years and join with 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 query is built in two parts:

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

Since birthdates older than 100 years are very unlike, the second part in fact selects almost nothing and does not affects performance too much.

Upvotes: 0

dnagirl
dnagirl

Reputation: 20456

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

This will do it in MySQL. I haven't added the anniversary comparison because I don't know the structure of your tables.

Upvotes: 1

Alex Barrett
Alex Barrett

Reputation: 16455

I am not quite sure how to interpret your question, but it seems you are after something like this.

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

It is possibly not exact as I don't know which flavour of SQL you are using.

Upvotes: 0

marc_s
marc_s

Reputation: 754298

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

That should work in SQL Server 2000 and up.

Marc

Upvotes: 7

Related Questions