Sql Query for retrieving the Records which are 3 months old

Question

Hi Experts

I have a problem with writing a sql query

I have a table in my database which holds the datetime stamp by name DateCreated(Column Name)
I have other column in my table which is Id.
I wanted to retrieve the Id Records which the DateCreated is not null and DateCreated should be less than three months

Here is the format of my Date 6/24/2011 10:56:03 AM
Any suggestions
Please Help

Vulpes · Answer

Try this query: SELECT Id FROM TABLE WHERE DATEDIFF(month ISNULL(DateCreated DATEADD(month -3 GetDate())) GetDate()) As you'll see if DateCreated is NULL it's replaced by a date 3 months ago and so shouldn't then feature in the query set.

Javeed M Shaikh · Answer

that's exactly i was thinking lets wait for karthik to reply.

Vulpes · Answer

Actually I'm wondering now whether that's what he really meant as his sample date is more than 4 months ago. Anyway doubtless he'll clarify that in due course.

Javeed M Shaikh · Answer

shouldn't it be : SELECT Id FROM TABLE WHERE DateCreated is not null and DATEDIFF(month ISNULL(DateCreated DATEADD(month -3 GetDate())) GetDate()) > 2

karthik v · Answer

Suthish i tried to query your solution but the query was returning the Id which are older than 3 months. I modified the query with changing the less than symbol to greater than symbol and it worked as the Datecreated should be greater than the comparison Date Correct me if am wrong. I am still a beginner.

karthik v · Answer

Thank You Guys for the Quick Responses. What i need to do is delete the records in the database which are more than 3 months old . So i tried few queries which i created but none of them worked for me.At that point of time i was trying to write a query for retrieving the records from the database which are less than three months old . And now i have the different ways of writing the same query which really improves my knowledge base. Thanks to you guys You Rock............

Suthish Nair · Answer

why don't like this.. SELECT Id FROM TABLE WHERE DateCreated is not null and DateCreated If want to remove time refer following methods.

Javeed M Shaikh · Answer

ok

Vulpes · Answer

I thought Karthik said less than 3 months (from now).