How do i get the dates in between using codeigniter and sql? with datatables

Question

THIS IS MY MODEL:

public function GetAttendance($from, $to)
{

    $sql = "SELECT a.empnum,CONCAT(a.name,' ',a.midname,' ',a.lastname) AS
            NAME,CONCAT(b.indate,' ',b.intime) AS 'TIMEIN',CONCAT(b.outdate,'
            ',b.outtime)AS 'TIMEOUT', DATEDIFF('timeout','timein') AS 'DUR' 
            FROM employees AS a
            JOIN times AS b ON (a.empnum=b.userid)
            WHERE b.indate BETWEEN
            STR_TO_DATE('".$from."','%m/%d/%y') AND STR_TO_DATE('".$to."','%m/%d/%y')";

    $query = $this->db->query($sql);

    return $query->result();
}

}

My Controller:

public function goEmployee() 
{ 
    $username = $this->session->userdata('username');

    $this->load->model('Model_attendance');

    $query = $this->Model_attendance->getOne($username);

    $data['EMPLOYEES'] = null;

    $data['isAdmin'] = false; //that will check if the user is admin or not
    if ($query) {
        $data['EMPLOYEES'] = $query;
    }

    $this->load->view('imports/header');
    $this->load->view('imports/menu');

    $this->load->view('employee', $data);
}

An error message pops up when I filter the dates from two different days with this on it

[{"empnum":"2","NAME":"Jon B. Pueblo","TIMEIN":"2016-01-22 16:06:08","TIMEOUT":"2016-01-24\r\n 15:13:13","DUR":null}, {"empnum":"2","NAME":"Jon B. Pueblo","TIMEIN":"2016-01-25 21:07:43","TIMEOUT":"2016-01-25\r\n 21:13:22","DUR":null}

AND this is my view

Ive made it into an image since stack overflow wont allow me to post it VIEW

Im using phpmyadmin right now. this message comes out [{"empnum":"2","NAME":"Jon B. Pueblo","TIMEIN":"2016-01-22 16:06:08","TIMEOUT":"2016-01-24\r\n 15:13:13","DUR":null},{"empnum":"2","NAME":"Jon B. Pueblo","TIMEIN":"2016-01-25 21:07:43","TIMEOUT":"2016-01-25\r\n 21:13:22","DUR":null},{"empnum":"4","NAME":"Ross D. Geller","TIMEIN":"2016-01-26 08:22:19","TIMEOUT":"2016-01-26\r\n 08:23:03","DUR":null},{"empnum":"4","NAME":"Ross D. Geller","TIMEIN":"2016-01-28 23:07:45","TIMEOUT":"2016-01-28\r\n 23:07:51","DUR":null}]. — Eknal Zerpe, Jan 28 '16 at 15:22
a datatable is suppose to be there but i have no idea what went wrong — Eknal Zerpe, Jan 28 '16 at 15:22
why are there line breaks in your `TIMEOUT` value? `2016-01-24\r\n` is not a valid date/time value in mysql. — Marc B, Jan 28 '16 at 15:23
Could you please remove the "javascript" tag? (I'd propose an edit myself, but apparently there's another pending edit blocking me from doing it) — Hayden Schiff, Jan 28 '16 at 15:27

score 0 · Answer 1 · answered Jan 28 '16 at 15:34

0

DATEDIFF('timeout','timein'), timeout and timein are not datetime format, they are strings, check if DATEDIFF can works with strings or the expected values must be cast-ed as dates.

or try DATEDIFF(b.indate,b.outdat)

answered Jan 28 '16 at 15:34

JassyJov

204
1
9

score 0 · Answer 2 · edited May 23 '17 at 12:16

You have to cast or convert your fields to date or datetime. You should change your where to something like this:

 set dateformat dmy 
 SELECT * from employees AS a
        JOIN times AS b ON (a.empnum=b.userid) 
        WHERE cast(b.indate as datetime) BETWEEN
        CAST('".$from."' as datetime) AND CAST('".$to."' as datetime)";

please notice that if you need another dateformat you have to change it. Also if you need a specific datetime format you will need to use CONVERT instead of CAST see this for more info

How do i get the dates in between using codeigniter and sql? with datatables

2 Answers2