CODEWITHSUNDEEP
mysqlsqldatetime
Rick
Rick

Reputation: 89

Date ranges in SQL

I'm having trouble coming up with this solution logically. I have an accounts table with a datetime trial_expiration_date column. I'd like to return all accounts that have been expired for at least two weeks but no more than one month using this column. How can I achieve this?

Upvotes: 0

Views: 64

Answers (3)

cctan
cctan

Reputation: 2023

You can do

select *
from t
where 
     datediff(date_add(trial_expiration_date, interval 2 week), now()) < 0
     and
     datediff(date_add(trial_expiration_date, interval 1 month), now()) > 0

It only calculate the date parts only and does not consider time.

Upvotes: 0

nolt2232
nolt2232

Reputation: 2644

Something like this should work. Just select all records where the expiration date is between two weeks ago and one month ago.

select * 
from accounts 
where trial_expiration_date between DATE_SUB(curdate(), INTERVAL 1 MONTH) 
    and DATE_SUB(curdate(), INTERVAL 2 WEEK)

Upvotes: 2

Thit Lwin Oo
Thit Lwin Oo

Reputation: 3438

How about this..

select *
from TABLE
where trial_expiration_date between dateadd(day,-14,getdate()) and getdate()

Upvotes: 1

Related Questions