Issue with getting date to sort correctly

Question

I built a program and noticed that in 2018 the sorting is not correct. I fixed it so it would show 2018 first but it still having an issues sorting correctly now it is showing example 1/02/2018 then 1/03/218 and so on, but I need it to show 1/03/2018 then 1/2/2018 then all of 2017 after. Here is my partial code for the model if anymore code is needed I will add it.

Model.php

<?php
    defined('BASEPATH') OR exit('No direct script access allowed');

    class visitor_log_model extends CI_Model {

        var $table = 'visitor_log_list';
        var $column = array('date','name','vendor','department','contact_person','expected_arrival_time'); //set column field database for order and search
        var $order = array('id' => 'desc'); // default order 

        public function __construct()
        {
            parent::__construct();
            $this->load->database();
        }
    //gets the gets all the records from the database

        private function _get_datatables_query()
        {

            $this->db->from($this->table);
            $this->db->order_by("date", "asc");
            $this->db->order_by("expected_arrival_time", "asc");


            $i = 0;

            foreach ($this->column as $item) // loop column 
            {
                if($_POST['search']['value']) // if datatable send POST for search
                {

                    if($i===0) // first loop
                    {
                        $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND. 
                        $this->db->like($item, $_POST['search']['value']);
                    }
                    else
                    {
                        $this->db->or_like($item, $_POST['search']['value']);
                    }

                    if(count($this->column) - 1 == $i) //last loop
                        $this->db->group_end(); //close bracket
                }
                $column[$i] = $item; // set column array variable to order processing
                $i++;
            }

Are those "date" columns c really date types? If not, they should be; you'd get this for free. — erik258, Jan 03 '18 at 22:25
I just check the database it is setup as navchar I had it that way because I am using a date picker does it matter if I need to change to date — Donny, Jan 03 '18 at 22:29

score 3 · Accepted Answer · answered Jan 03 '18 at 22:37

3

It is a very bad idea to store a date as a string! Clumsy, slow and erronous...

For SQL Server's engine a string is a string. You can name the column date and you can store values, that look like a date, but sorting them will be done according to the rules of alphanumeric sorting (like in a lexicon).

Furthermore: A date format like 1/02/2018 is culture dependant. Some systems will take this as first of Feb, others as second of Jan. This might work perfectly for you and pass all internal tests, but breaks on a customer's machine with silly errors.

Store any value with the appropriate type! Use DATETIME2 or DATE with a modern SQL-Server!
If you have to use a string format (like in serialisation) you should stick to secure formats! Best is ISO8601, which is in most cases YYYY-MM-ddTHH:mm:ss (beware of the T in the middle).
Think about a date's format (its visual representation) only in your presentation layer!

answered Jan 03 '18 at 22:37

Shnugo

66,100
9
53
114

so should I change it to date the only issue is I am using a datepicker and it puts the date in a certain way – Donny Jan 03 '18 at 22:39
Yes! You should store the value as a date type! Your datepicker will either allow to retrieve a typed value or your language will allow to parse a *real* date out of a string. You can change the column to `DATETIME2` or `DATE` and still transfer your data as strings. But use a secure format! [Read this](https://stackoverflow.com/a/33230070/5089204) and [this](https://stackoverflow.com/a/39504506/5089204) – Shnugo Jan 03 '18 at 22:44
You were right I created a new table and changed the type in the column to date and transferred the data over and it works fine now – Donny Jan 03 '18 at 23:06

Issue with getting date to sort correctly

1 Answers1