Creating a statement related to date

Question

I am having a hard time creating a SQL Statement.

I need to be able to determine one month from today.   Meaning that if today is April1st then one month past would be March the 1st.  I'm ultimately using this as a way to count how many movies have been watched in the past month.

Select [dateview] from [table] where getdate - 10 years???

Answer 1

I need to be able to determine one month from today.   Meaning that if today is April1st then one month past would be March the 1st.  

    Try

   

SELECT DATEADD(month, 1, '2012-08-30')
SELECT DATEADD(month, 1, getdate())

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Thanks Manish :)

This won't help me however as I need the date to be generic, meaning that it can't have a hard coded date in it so that I could calculate 1 month anytime, plus I need it in one select statement.

Answer 3

Mike,

Yeah you can definitely use the generic dates, I created a  sample table, I hope this is what you are looking for.

 

Create table yourtable(moviedid int,moviename varchar(20),datewatched datetime)

Insert into yourtable values(1,'Titanic','2012-03-07')
Insert into yourtable values(2,'Jurassic Park','2012-03-08')
Insert into yourtable values(2,'Jurassic ParkII','2012-03-09')
Insert into yourtable values(3,'New Movie','2012-04-08')

select * from yourtable

select * from yourtable where datewatched >=DATEADD(month, -1, getdate())

 

---

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 4

Thanks Manish ;)

It works great.