日期列是varchar如何选择数据，直到今天01-01-2019

Question

I have a problem with fetch data. Date is saved in VARCHAR, which means it will string. I want to select data from 01-01-2019 to today in my table. So I have problem in where class condition.

Please check code below where I am trying to fetch data but it's still not working. I am using STR_TO_DATE(holiday_date, '%d-%m-%Y') for change format string to date column.

<?php 
echo  $edate=date('d-m-Y');
$query="SELECT * FROM holidays where STR_TO_DATE(holiday_date, '%d-%m-%Y')='$edate' ORDER BY STR_TO_DATE(holiday_date, '%d-%m-%Y') asc ";
$stmt=$conn->prepare($query);
$stmt->execute();
$i=1;
    while ($result = $stmt->fetch()) {
    ?>
<tr class="holiday-completed">
    <td><?php echo $i;?></td>
    <td style="display: none;"><?php echo $result['id'];?></td>
    <td><?php echo $result['title'];?></td>
    <td><?php echo $result['holiday_date'];?></td>
    <td>
        <?php $s=$result['holiday_date'];
                $sdate = strtotime($s);
                echo date('l',$sdate);
        ?>
    </td>

Answer 1

A query like this, then:

SELECT * 
FROM holidays 
WHERE STR_TO_DATE(holiday_date, '%d-%m-%Y') BETWEEN STR_TO_DATE('01-01-2019', '%d-%m-%Y') AND NOW()
ORDER BY STR_TO_DATE(holiday_date, '%d-%m-%Y') asc

But really, you should sort your data out;

• make a new column of date type,
• run a query like UPDATE table SET holiday_date_PROPER = STR_TO_DATE(holiday_date, '%d-%m-%Y') to prepopulate
• create a trigger to convert the string to date once (upon insert or update) so that future insertions and updates are also handled,
• query the date column not the varchar column (saves hundreds of conversions that are necessary every time you run your select query - over your app's life it will save millions of conversions),
• switch your app to using the date column,
• delete the varchar column