CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Haseem hac
Haseem hac

Reputation: 31

mysql date comparison using timestamp retuns wrong answer

I am trying get data from MySQL using date between 2015 February 2 ,starting to ending using this query but MySQL returns all rows in February 

SELECT CustomerID, 
    CustomerName, 
    DATE, 
    Doller,
    Minute ,
    Rate,Total, 
    NULL AS Amountofpaying
FROM saletrack where CustomerID=1
UNION ALL 
SELECT CustomerID, 
    CustomerName, 
    DATE, 
    NULL AS Doller,
    NULL AS Minute, 
    NULL AS Rate, 
    NULL AS Total, 
    Amountofpaying
FROM paymentdetails 
where CustomerID=1 and  `date` BETWEEN '2015-02-02 00:00:01' AND '2015-02-02 23:59:59'
ORDER BY DATE

please look images I commented below

Upvotes: 0

Views: 42

Answers (1)

Marcelo Keiti
Marcelo Keiti

Reputation: 1240

Add the timestamp condition in the select from saletrack:

SELECT CustomerID, 
    CustomerName, 
    DATE, 
    Doller,
    Minute ,
    Rate,Total, 
    NULL AS Amountofpaying
FROM saletrack where CustomerID=1 and `date` BETWEEN '2015-02-02 00:00:01' AND '2015-02-02 23:59:59'
UNION ALL 
SELECT CustomerID, 
    CustomerName, 
    DATE, 
    NULL AS Doller,
    NULL AS Minute, 
    NULL AS Rate, 
    NULL AS Total, 
    Amountofpaying
FROM paymentdetails 
where CustomerID=1 and  `date` BETWEEN '2015-02-02 00:00:01' AND '2015-02-02 23:59:59'
ORDER BY DATE

Upvotes: 1

Related Questions