Bob Mwenda Bob Mwenda - 5 months ago 18
PHP Question

MYSQL SELECT statement using DATETIME with no time

This works perfectly.

SELECT DISTINCT customers FROM customertable WHERE time_paid BETWEEN '2016-06-28 10:27:55' AND '2016-07-05 14:07:51';

However since I'm using javascript datepicker to for the user to pick the date periods and has only dates but no time this apparently does not work
SELECT DISTINCT customers FROM customertable WHERE time_paid BETWEEN '2016-06-28' AND '2016-07-05';


My select statement is this: $t1 is lower date while $t2 is upper date.

$sql = "SELECT DISTINCT customertable FROM customer WHERE time_paid BETWEEN '$t1' AND '$t2'";

$result = mysqli_query($conn, $sql);
$rowcount = mysqli_num_rows($result);


..

Is there a way I can insert to concatenate the date say
'$t1'.12.00.00
on my select statement to take care of the full
DATETIME
entry on my database when selecting?

Answer

use mysql function date add

mysql> SELECT '2008-01-02';
+------------+
| 2008-01-02 |
+------------+
| 2008-01-02 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT ADDDATE('2008-01-02', INTERVAL 0 HOUR);
+----------------------------------------+
| ADDDATE('2008-01-02', INTERVAL 0 HOUR) |
+----------------------------------------+
| 2008-01-02 00:00:00                    |
+----------------------------------------+
1 row in set (0.00 sec)


SELECT DISTINCT customertable FROM customer WHERE time_paid BETWEEN ADDDATE('$t1',INTERVAL 0 HOUR) AND ADDDATE('$t2',INTERVAL '23:59' HOUR_MINUTE)";