CODEWITHSUNDEEP
mysql
ShoeLace1291
ShoeLace1291

Reputation: 4698

Why is my SQL returning 0 rows when comparing the current time to a timestamp column?

I want to determine if a user has an active subscription. So the activation_date column must be less than the current time and the expiration_date column must be greater than the current time. The query always returns 0 results. Why is this? Here is my MySQL statement:

SELECT 
    `s`.`subscription_id`, `s`.`plan_id`, `s`.`member_id`, `s`.`activation_date`, `s`.`expiration_date`, `s`.`period` as cycle, `s`.`amount`, 
    `p`.`plan_id`, `p`.`title` as plan_title 
FROM (`subscriptions` as s)
LEFT JOIN `plans` as p ON `s`.`plan_id` = `p`.`plan_id` 
WHERE `s`.`member_id` = '1' 
    AND `s`.`activation_date` <= 'NOW()' 
    AND `s`.`expiration_date` >= 'NOW()' 
LIMIT 1

Here are the table results:

enter image description here

Upvotes: 1

Views: 35

Answers (1)

Holger Just
Holger Just

Reputation: 55758

You are not comparing the columns to the current timestamp (i.e. the return value of NOW()) but to the actual string 'NOW()'. In order to use the result of the function, you have to get rid of the quotes.

Upvotes: 4

Related Questions