Select Records 18 Months Before Curernt Date 2

[larrydavid]

Hello,

In SQL Server 2005 I am trying to select a date 18 months before the current date:

select * from dbo.TEST
where DATEADD(datepart(month,TESTDATE), -18, getdate())

I really tried to figure this one out on my own. I think I'm pretty close, but I keep getting: "Invalid parameter 1 specified for dateadd."

Can someone please show me where I'm going wrong?

Thanks,
Larry

 

[r937]

DATEADD's first paramter should be the keyword MONTH

the second parameter should be the number of months to add, i.e. -18 (to go back in time)

then you need to actually set a comparison condition

WHERE testdate < DATEADD(MONTH,-18,GETDATE())

this returns all testdates which are older than 18 months ago

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[drlex]

Be aware that if your date contains hours and minutes, you can get a different result set running the query in the afternoon as opposed to the morning.

This will strip the hours and minutes from the comparison date

WHERE testdate < DATEADD(MONTH,-18,cast(cast(getdate() as char(12)) as smalldatetime))

soi là, soi carré

 

[larrydavid]

Thank you all very much! Now I can move on and will know this for the next time.

Larry