用活动记录检查两个日期是否相等 - check if two dates are equal in codeigniter with Active Record

check if two dates are equal in codeigniter with Active Record

我想检查两个日期是否相等，是$query_array['datepicker']和absensi_tanggal。它们有不同的格式，所以i CONVERT和absensi_tanggal。

SQL自动转换日期吗?我试图不转换它，但查询不工作，它总是返回0行。

所以我试了这个代码，但它不工作。

$q = $this->db->select('*', 'CONVERT(VARCHAR(10), absensi_tanggal, 101) AS temp')->from('msabsensi')->limit($limit, $offset)
             ->join('msumat', 'msabsensi.umat_id = msumat.umat_id')
             ->join('mskelas', 'mskelas.kelas_id = msumat.kelas_id');
$q->where('CONVERT(VARCHAR(10), absensi_tanggal, 101)', $query_array['datepicker']);
$result['rows'] = $q->get()->result();

这是我得到的错误(我认为我在select()中做的CONVERT AS不起作用):

Error Number: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'VARCHAR(10), absensi_tanggal, 101) '04/01/2013' LIMIT 10' at line 5
SELECT * FROM (`msabsensi`) JOIN `msumat` ON `msabsensi`.`umat_id` = `msumat`.`umat_id` JOIN `mskelas` ON `mskelas`.`kelas_id` = `msumat`.`kelas_id` WHERE CONVERT(VARCHAR(10), absensi_tanggal, 101) '04/01/2013' LIMIT 10
Filename: C:'xampp'htdocs'ci_gabdb'system'database'DB_driver.php
Line Number: 330

问题是temp不用作此查询中任何列的别名，您在where子句

中引用它

$this->db->select('*')
->from('msabsensi')->limit($limit, $offset)
->join('msumat', 'msabsensi.umat_id = msumat.umat_id')
->join('mskelas', 'mskelas.kelas_id = msumat.kelas_id')
//->where('temp = '.$query_array['datepicker']) // here
->where('absensi_tanggal = DATE_FORMAT( STR_TO_DATE(  "' .$query_array['datepicker'] . '",  ''%m/%d/%Y'' ) ,  ''%Y-%m-%d'' )') 
->get()->num_rows();

编辑

使用别名检查问题

在MySQL查询的WHERE子句中使用列别名会产生错误