CODEWITHSUNDEEP
phpmysqlsql-serverdatetime
Bob Mwenda
Bob Mwenda

Reputation: 89

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 for the user to pick the date periods and have only dates but no times 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 a concatenated date, say '$t1'.12.00.00, on my select statement to take care of the full DATETIME entry on my database when selecting?

Upvotes: 0

Views: 42

Answers (1)

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

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)";

Upvotes: 1

Related Questions