CODEWITHSUNDEEP
sql-serverreporting-services
Patrick
Patrick

Reputation: 63

SQL Server : find upcoming dates

I have a SQL Server database with a Date column. These dates are all future dates.

How would I go about finding all approaching dates within a 2 week time period? Basically a two week notice query on upcoming dates.

For example: Today is August 2nd. The query would return, if any, dates that were August 16th (2 weeks ahead).

And if it was run tomorrow it would return August 16th and August 17th (2 weeks ahead).

This would obviously be run everyday via a reporting manager (SSRS). Any ideas?

Thanks.

Upvotes: 0

Views: 205

Answers (2)

Richard
Richard

Reputation: 30618

You can use DATEADD:

SELECT *
FROM MyTable
WHERE MyDateField BETWEEN GETDATE() AND DATEADD(week, 2, GETDATE())

If your column is datetime, you may want to convert it to be a date first, otherwise running this at 12:00 on Aug 1st would return everything upto 12:00 on Aug 15th. The following code will do this for you:

SELECT *
FROM MyTable
WHERE CAST(MyDateField AS date) BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(week, 2, GETDATE()) AS date)

Upvotes: 1

Kermit
Kermit

Reputation: 34055

SELECT * FROM mytable WHERE dateCol BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND 
    DATEADD(s, -1, DATEADD(d, 1, DATEADD(d, DATEDIFF(d, 0, DATEADD(wk, 2, GETDATE())), 0)))

This will return anything between the start of today and the end of the day in 2 weeks.

If you are trying to return any records for a single day (00:00:00 > 23:59:59) 2 weeks from today, then use

SELECT * FROM mytable WHERE dateCol BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(wk, 2, GETDATE()))) AND 
    DATEADD(s, -1, DATEADD(d, 1, DATEADD(d, DATEDIFF(d, 0, DATEADD(wk, 2, GETDATE())), 0)))

You can replace GETDATE() with whatever date you want to start/end with.

Upvotes: 0

Related Questions