select only date form datetime field in where condation-CodePudding

i have stored this kind of format 2022-02-06 18:40:00 in my trans_reminder_date i want to use only date in where condition but with this condition i am not able to fetch data

$today = date('Y-m-d');

SELECT * FROM sales_detail 
   WHERE  trans_reminder_date = '".$today."' 
   AND trans_reminder_date != ''
   ORDER BY sales_detail_id DESC";

CodePudding user response：

If when your filter parameter is a string:

select * from sales_detail  
where cast(trans_reminder_date as date) = cast('2020-03-22' as date)

if you want to use the current date for filtering then MySQL has a function that getting only the current date without time.

select * from sales_detail  
where cast(trans_reminder_date as date) = curdate()

On MySQL for converting other types to another, you can use a cast

P.S. Starting with MySQL 8.0.13 we have now an easiest way to create functional indexes. When you are using cast(updated_at as date) then DB will not use index for column updated_at. You must create a functional index for best performance.

CodePudding user response：

The following query will give you all the result for the current date. By doing this there won't be any need to cast values.

SELECT
    *
FROM
    sales_detail
WHERE
    trans_reminder_date >= curdate()
    AND trans_reminder_date < curdate()   INTERVAL '1' DAY
ORDER BY
    sales_detail_id DESC;

CodePudding user response：

Using Cast function to change datetime type to date example

Cast(column_name as date)

Or You using convert function change datetime type to date

CONVERT(column_name, date);
CONVERT(expression, datatype);  
OR,  
CONVERT(expression USING character_set);

character_set: It specifies the desired character set in which we want to be converted.

Data type : It specifies the desired data type in which we want to be converted.

Expression : It is a specified value going to be converted into another specific datatype.