CODEWITHSUNDEEP
mysqlmariadb
imoola
imoola

Reputation: 3

Fetch values between two dates - MariaDB version: 10.1.37

I'm trying to fetch values between two dates, specifically 24hrs 

SELECT * 
FROM `transactions` 
WHERE accnum = '1534610376' 
    AND tdate BETWEEN 20190311 AND 20190312

This query works fine but, i don't want it for a constant date, i have checked and seen many format but none seems to work. please help

Upvotes: 0

Views: 341

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31792

If you "want records from today alone" - a simple way would be:

WHERE accnum = '1534610376' 
    AND DATE(tdate) = CURRENT_DATE()

However - To utilize an index, a column should not be wrapped into a function. So an efficient way would be

WHERE accnum = '1534610376' 
    AND tdate >= CURRENT_DATE()
    AND tdate <  CURRENT_DATE() + INTERVAL 1 DAY

A good index for this query would be INDEX(accnum, tdate).

Upvotes: 1

Arnaud Peralta
Arnaud Peralta

Reputation: 1305

I suggest you to put your date between quots like this: 

SELECT * 
FROM `transactions` 
WHERE accnum = '1534610376' 
    AND tdate BETWEEN '20190311' AND '20190312'

After, you can define a user defined function like this :

CREATE FUNCTION BetweenDate(@toCompare VARCHAR(30), @rightDate DATE, @leftDate DATE)  
RETURNS TABLE
AS   
BEGIN  
    RETURN (
        SELECT *
        FROM transactions
        WHERE accum = @toCompare AND tdate BETWEEN @rightDATE AND @leftDate
    )
END;

Upvotes: 0

Related Questions