CODEWITHSUNDEEP
mysqlsqldatetimeselectbetween
user2657778
user2657778

Reputation: 105

MySQL find records using between

Here my code:

SELECT * 
FROM  `products`
WHERE
DATE_ADD( UTC_TIMESTAMP( ) , INTERVAL 7 HOUR ) 
BETWEEN DATE_SUB( STR_TO_DATE( IsPromotionDate,  '%m/%d/%Y %H:%i' ) , INTERVAL 3 DAY ) 
AND STR_TO_DATE( IsPromotionDate,  '%m/%d/%Y %H:%i' )

But the results return all the products that before IsPromotionDate but not after IsPromotionDate - 3. I still haven't figure it out why.

Upvotes: 2

Views: 62

Answers (2)

Arun Killu
Arun Killu

Reputation: 14263

SELECT * 
FROM  `products`
WHERE
STR_TO_DATE( IsPromotionDate,  '%m/%d/%Y %H:%i')
BETWEEN NOW()
AND  DATE_SUB( now() , INTERVAL 3 DAY )

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT * FROM  `products`
WHERE DATE_ADD(UTC_TIMESTAMP( ), INTERVAL 7 HOUR) BETWEEN DATE_SUB(IsPromotionDate, INTERVAL 3 DAY) AND IsPromotionDate

OR

SELECT * FROM  `products`
WHERE DATEDIFF(DATE_ADD(UTC_TIMESTAMP(), INTERVAL 7 HOUR), IsPromotionDate) BETWEEN 0 AND 3

Upvotes: 1

Related Questions