Hi everyone. I am looking to compare dates in SQL - do a bit of arithmetic on them. What I have is:

Two events - Buying a car, and Servicing the car. I am interested in the people who have had to take their car in for a service within one month of buying their car. So the code I have is:



So my logic is that if the service date - the purchase date is between 1 and 30, then they have taken their car in for a service within the first 30 days of having it.

Any help with that last line of code?

Thanks in advance!

I have altered the last line to

but this does not take into account they years and months. I am currently getting dates that the days are up to 30 days apart, the results bring back different years from past cars. But I need the years and months to be fixed (unless of course the car was bought in a different month)

try days(B.EVENT_DT) - days(A.EVENT_DT) <= 30
this should help

When I run that query, I am getting dates like:

Purchase Date-----Service Date

2001-10-13 -------2004-04-26

which is fine on the day criteria, but as you can see, the service date here is not within 30 days after the purchase date, it is 2 years, 6 months AND within 30 days. Is there anyway around this?

DAY and DAYS are completely different functions.

have you tried DAYS (returns date translated in nbr of days) and not DAY (returns the day of this date)

Both DAY and DAYS are bringing back dates which are over 30 days apart (ie three years apart or a number of months apart). I even checked the day column alone by changing the day diff to 10, and I am getting dates where the number of days difference is greater than 10.

am I putting the where clause in the correct place?

I combined it with the same querys for year and months according to my criteria and it worked fine.

you are joining the table, but not specifying the join condition
on a.customer=b.customer
something like this :
SELECT * FROM
FROM Database A, database b
WHERE A.EVENT_type = "Purchase"
and B.EVENT_type = "Service"
and a.customer = b.customer
and days(B.Event_date) - days(A.Event_date) BETWEEN 1 AND 30