CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
GabrielVa
GabrielVa

Reputation: 2388

SQL Server : create future dates

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.

Upvotes: 5

Views: 12523

Answers (2)

Marco
Marco

Reputation: 57593

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)

Upvotes: 13

mdm
mdm

Reputation: 12630

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.

Upvotes: 3

Related Questions