PHP Date stored in VARCHAR in format DD/MM/YYYY needs to be sorted in ASC ORDER in SQL query

Question

as the title suggests the date format in the database in a table is stored as DD/MM/YYYY format in VARCHAR (with slashes). I want to sort the results in ascending order only. But my query is not allowing me to do so. A simple ORDER by order_date ASC does not work. The developer who did this before made this mistake of not inserting it in DATE format. Now the problem is that it has thousands of records which cannot be edited now one by one. I therefore need to have a solution to display it in ascending order by writing a PHP function. But I do not know how to. Can anybody please help?

SQL

<?php

                $this->db->select('str_to_date(your_col, '%d/%m/%Y') as my_date');
                $this->db->order_by('my_date','ASC');
                $this->db->where('customer_id',$id);
                $qry3 = $this->db->get('due');

                    foreach ($qry3->result() as $row){

                $total=$total+ ($row->due-$row->paid_amount);
                $total=$total-$row->discount_allowed;
                $total=$total+$row->credit_note_amount;
                ?>


                    <tr>
                        <td><?php echo $row->my_date?> </td>
                        <td><?php echo $row->order_no?></td>
                        <td>
                        <?php
                        if (isset($row->due)) {
                           echo "SALE A/C";
                        } else if (isset($row->particulars)) {
                          echo $row->particulars;
                        } else {
                            echo $row->particulars;
                        }
                        ?>
                        </td>
                        <td align="">
                        <?php if (isset($row->due))
                            {
                            echo $row->due;
                            } else {
                                echo $row->credit_note_amount;
                            }
                        ?>
                         </td>
                        <td align="">
                        <?php

                        if (isset($row->paid_amount)) {
                          echo $row->paid_amount;
                        } else if (isset($row->discount_allowed)) {
                            echo $row->discount_allowed;
                        } else {

                        }
                         ?> </td>
                        <td class="info"><?php echo $total?></td>



                    </tr>

                    <?php }?>

saving date in `varchar` format is wrong table structure design. Use `timestamp`,'date',`date-time` type . It will make your query processing easy — Alive to die - Anant, Mar 30 '18 at 12:04
@AlivetoDie that is what the mistake previous developer did.. I have no option now. It has thousands of records — Single Mingle, Mar 30 '18 at 12:06
this will be helpful for you:- https://stackoverflow.com/a/5080410/4248328 — Alive to die - Anant, Mar 30 '18 at 12:08

ScaisEdge · Answer 1 · 2018-03-30T12:54:03.110

0

You can simply convert your varchar column as a date

  select str_to_date(your_col, '%d/%m/%Y') my_date
  from your_table  
  order by my_date ASC


  $this->db->select(' paid_amount,  discount_allowed,  str_to_date(your_col, '%d/%m/%Y') as my_date');
  $this->db->order_by('my_date','ASC');

edited Mar 30 '18 at 12:54

answered Mar 30 '18 at 12:06

ScaisEdge

131,976
10
91
107

This the query coming SELECT str_to_date(order_date, `'%d/%m/%Y')` as my_date FROM (`due`) ORDER BY `my_date` ASC – Single Mingle Mar 30 '18 at 12:31
I have updated my PHP code in the question.. can you check and tell the error I am making in query and fetching the date there? – Single Mingle Mar 30 '18 at 12:37
you are not selecting any of the column you try to echo .. you are just select the my_date .. .. you are changing question each steps this is not right and is not fair ..this is not a coding service .. if you have not error mean the my suggestion work ... but the code you need is your problem . and is not related to this question . – ScaisEdge Mar 30 '18 at 12:42
could be you need to add the column need you really need .. i have a pair for suggestion .. answer updated with column paid_amount, discount_allowed added – ScaisEdge Mar 30 '18 at 12:54

score 0 · Answer 2 · answered Mar 30 '18 at 12:08

0

You can try using STR_TO_DATE of MySQL

SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y') as newcolumnname
FROM    yourtable order by newcolumnname asc

answered Mar 30 '18 at 12:08

Ashish Malvadkar

3
4

how to do this in code ignitor? I have given a sample in question – Single Mingle Mar 30 '18 at 12:13
Add the column in your `SELECT` clause? – Salman A Mar 30 '18 at 12:29

PHP Date stored in VARCHAR in format DD/MM/YYYY needs to be sorted in ASC ORDER in SQL query

2 Answers2