CODEWITHSUNDEEP
mysqlwhere-clausebetween
Vinsens
Vinsens

Reputation: 199

Different Date Format in BETWEEN condition

Im newbie for sql. My code:

$start_date='2017-01-01';
$end_date='2016-01-31';
$this->db->where('date_note BETWEEN "'. date('Y-m-d', strtotime($start_date)). '" and "'. date('Y-m-d', strtotime($end_date)).'"');

But in my database is like 2016-03-11 14:21:36

How I can get this record from database with different format date? Thanks

Upvotes: 1

Views: 404

Answers (2)

Dylan Su
Dylan Su

Reputation: 6065

There is no problem in your SQL. It is okay to compare DATE with DATATIME(as in your database).

Here is a demo:

mysql> SELECT '2016-03-11 14:21:36' BETWEEN '2016-03-10' AND '2016-03-12';
+-------------------------------------------------------------+
| '2016-03-11 14:21:36' BETWEEN '2016-03-10' AND '2016-03-12' |
+-------------------------------------------------------------+
|                                                           1 |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 0

Andath
Andath

Reputation: 22724

The MySQL function DATE_FORMAT() resolves fully your problem as it allows you to get the time you saved within your database in the format of your choice.

Upvotes: 1

Related Questions