Comparing date columns in Sql

[tamnor]

Hi

I have a database table that has to date columns. A purchase_start_date and a purchase_end_date. I want to write a query to find all rows where the purchase_end_date is no longer than 90 days after the purchase_start_date. My dates are in Unix time.

Can anyone help me?

[dafodil]

Hi

I have a database table that has to date columns. A purchase_start_date and a purchase_end_date. I want to write a query to find all rows where the purchase_end_date is no longer than 90 days after the purchase_start_date. My dates are in Unix time.

Can anyone help me?

If you stored it in Timestamp you can easily compare it Subtracting the end-date to 90...

for example the statement
SELECT SUBDATE('1998-01-02 12:00:00', 31);
will return a value
'1997-12-02 12:00:00'

where 31= number of days....

Now the idea is to compare it with your own select statement....

 Select * FROM table where `purchase_start_date`>=SUBDATE(`purchase_end_date`, 90)

Something like that....

Why >= because it returns date....
If the result is equal to purchase_start_date It means that the days is equal to 90...
But if the result is greater than purchase_start_date it means that the days is less than 90....