How to get data between two date in sql when date save in type varchar (Y-m-d h:i:s) format ?

Answer sql query :

$date_from='2022-04-13 12:02:33';

$date_to='2022-04-10 12:02:33';

$date_from_second= strtotime(date($date_from));
$date_to_second= strtotime(date($date_to));
$datediff_second= $date_to_second- $date_from_second;
$datediff_days =round($datediff / (60 * 60 * 24));

select * from table_name where DATEDIFF('$date_to',STR_TO_DATE(created_date ,'%d-%m-%Y'))<=$datediff_days and DATEDIFF('$date_to',STR_TO_DATE(created_date ,'%d-%m-%Y'))>='0'

Note- Please replace table_name and created_date with the actual table, and date in the above SQL query

Problem with varchar date-

So the main problem here is that the date is saved in varchar format, for example, 2022-04-13 12:02:33 if apply normal query like

$date_from='2022-04-13 12:02:33';

$date_to='2022-04-10 12:02:33';

select * from table_name where created_date >='$date_from' and created_date <='$date_to'

it doesn't work it compares with the only first text of date ie 2022.

so we have to find another way here is the right way

Logic- From text of date(varchar) we can't compare data between two dates but we can find no of days between dates.

We use DATEDIFF() function in our SQL to count the number of days between two dates

select * from table_name where

DATEDIFF('$date_to',STR_TO_DATE(created_date ,'%d-%m-%Y'))<=$datediff_days and DATEDIFF('$date_to',STR_TO_DATE(created_date ,'%d-%m-%Y'))>='0'

SQL query with the value

select * from table_name where DATEDIFF('2022-04-13',STR_TO_DATE(created_date ,'%d-%m-%Y'))<=3 and DATEDIFF('2022-04-13',STR_TO_DATE(created_date ,'%d-%m-%Y'))>='0'

Explanation - Basically here the data of the last four dates will be found 2022-04-13,2022-04-12,2022-04-11, and 2022-04-10.

DATEDIFF() function will return negative value for date greater than 2022-04-13 so DATEDIFF('2022-04-13',STR_TO_DATE(created_date ,'%d-%m-%Y'))≤ 3 Returns true for a date greater than 2022-04-13 and includes the result in those dates

To eliminate this

we have to write DATEDIFF('2022-04-13',STR_TO_DATE(created_date ,'%d-%m-%Y'))>='0'

QueryFlag

How to get data between two date in sql when date save in type varchar (Y-m-d h:i:s) format ?