这是我的模型:
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();
}
}
我的控制器:
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);
}
当我过滤两个不同日期的日期时弹出一条错误消息
[{"empnum":"2","NAME":"Jon B. Pueblo","TIMEIN":"2016-01-22 16:06:08","TIMEOUT":"2016-01-24''r' 15:13:13","DUR":null}, {"empnum":"2","NAME":"Jon B. Pueblo","TIMEIN":"2016-01-25 21:07:43","TIMEOUT":"2016-01-25''r' 21:13:22","DUR":null}
这是我的观点
我把它变成了一个图像,因为堆栈溢出不允许我发布它 查看
DATEDIFF('timeout','timein
'),timeout 和 timein 不是 datetime 格式,它们是字符串,请检查 DATEDIFF 是否可以处理字符串或预期值必须转换为日期。
或尝试 DATEDIFF(b.indate,b.outdat)
您必须将字段转换为日期或日期时间。您应该将位置更改为如下所示的内容:
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)";
请注意,如果您需要其他日期格式,则必须更改它。此外,如果您需要特定的日期时间格式,则需要使用CONVERT而不是CAST,请参阅此处以获取更多信息