如何使用codeigniter和sql获取日期？与数据表

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}

[{“empnum”：“2”，“NAME”：“Jon B. Pueblo”，“TIMEIN”：“2016-01-22 16:06:08”，“TIMEOUT”：“2016-01-24 \ r \ n \ 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

我已经把它变成了一个图像，因为堆栈溢出不允许我发布它的视图

2 个解决方案

#1

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.

DATEDIFF（'timeout'，'timein'），timeout和timein不是日期时间格式，它们是字符串，检查DATEDIFF是否可以使用字符串，或者必须将期望值作为日期进行转换。

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

或尝试DATEDIFF（b.indate，b.outdat）

#2

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

请注意，如果您需要其他日期格式，则必须更改日期格式。此外，如果您需要特定的日期时间格式，则需要使用CONVERT而不是CAST，以获取更多信息

#1