SQL Server : create future dates

Question

How do I create future dates in SQL? For example, I want to be able to use my date range and show everything for just next month (purchase orders), then another for two months out, etc. I have used the fn NOW() for the current date/time but this doesn't help me at all for showing records for next month, etc.

Thanks

This is for a SQL query that in doing in SQL Server 2008 R2.

Answer 1

If you use MySQL you can use:

SELECT date_col FROM your_table
WHERE date_col BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 MONTH)

For MS-SQL (it should work):

SELECT date_col FROM your_table
WHERE date_col BETWEEN GETDATE() AND DATEADD(Month, 1, GETDATE())

For Oracle (it should work):

SELECT date_col FROM your_table
WHERE date_col BETWEEN SYSDATE AND add_months(SYSDATE, 1)

Answer 2

You can use the DATEADD function to create dates relative to another date. The following call will generate a date exactly one month in the future:

DATEADD(month, 1, GETDATE())

You can use negative numbers to go back as well as forward. There are many other increments you can use, e.g. year, day, week, quarter, millisecond etc.