CODEWITHSUNDEEP
sqlsql-server-2008dateadd
sql2015
sql2015

Reputation: 611

sql get data where date is greater than current month

i want to retrieve records where a date field is set to future months

does this look correct

Select * from table1 WHERE
 datesetto >MONTH(dateadd(dd, -1, GetDate())))

Upvotes: 0

Views: 8426

Answers (3)

Twinkles
Twinkles

Reputation: 1994

SELECT * FROM table1
WHERE datesetto >= DATEADD(month, DATEDIFF(month, 0, getdate())+1, 0)

Explanation:

  • DATEDIFF(month, 0, getdate()) calculates how many months have passed since 1900-01-01.
  • DATEADD(month, DATEDIFF(month, 0, getdate()), 0) returns the beginning of this month.
  • DATEADD(month, DATEDIFF(month, 0, getdate())+1, 0) returns the beginning of next month.

Upvotes: 1

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Try this:

  Select * from table1 WHERE
  ((DATEPART(MONTH,datesetto) > DATEPART(MONTH,GETDATE()) 
  AND DATEPART(YEAR,datesetto) = DATEPART(YEAR,GETDATE()))
  OR (DATEPART(YEAR,datesetto) > DATEPART(YEAR,GETDATE())))

DATEPART(Month,GETDATE()) will give the month of the current date and then you can compare it with the datesetto

Update: The above query will give data for any months greater than the current month and any month greater in the year than the current year.

Upvotes: 0

Clar Cleetus
Clar Cleetus

Reputation: 275

 select * from tablename where month(columndate)>month(getdate()) and 
 year(columndate)>=year(getdate())

Upvotes: 1

Related Questions