[RESOLVED] DateTime/SQL Query

Pilly

Hi,
I tried a search first before posting, just incase but nothing hits what i want.

I want to show all records of the current day after 4pm so 16:00 - 23:59

Heres what i got so far..
Pdate is the DateTime variable ; Format = 0000-00-00 00:00:00

$Today = date('Y-m-d');

$sql3 = "SELECT * , Count(picklist.Initial) AS CInitial from picklist
LEFT JOIN warehouseops ON picklist.Initial=warehouseops.Initial
WHERE PDate = '$Today'
GROUP BY picklist.Initial
ORDER BY CInitial DESC";

Any ideas on how to specify the time criteria as well within this sql statement?

liquorvicar

have you tried

$sql3 = "SELECT * , Count(picklist.Initial) AS CInitial from picklist
LEFT JOIN warehouseops ON picklist.Initial=warehouseops.Initial
WHERE PDate>= '2005-07-15 16:00' AND PDate< '2005-07-16 00:00'
GROUP BY picklist.Initial
ORDER BY CInitial DESC";

Pilly

I need to set it to the current day, not just 2005-07-15

liquorvicar

my post was just an example of how you could do it. you should be able to modify my suggestion to use the current date...

Pilly

I could easily do
$Today = date('Y-m-d');

SQL =xxxx
WHERE PDate = '$Today'

But i was after how do i get the time into this line

?
WHERE PDate = '$Today' 16:00

???

liquorvicar

just move the '

WHERE PDate = '$Today 16:00'

Pilly

Ok, im getting there

here's my updated SQL

$Today = date('Y-m-d');

$sql3 = " SELECT * , Count(picklist.Initial) AS CInitial from picklist
LEFT JOIN warehouseops ON picklist.Initial=warehouseops.Initial
WHERE PDate >= '2005-07-15 16:00' AND PDate <= '2005-07-15 23:59'
GROUP BY picklist.Initial
ORDER BY CInitial DESC
";

Im getting no records returned from yesterday date (as i cant test on todays yet), Does this look ok though?

Pilly

After some playing around, i got it working...

$Today = date('Y-m-d');

$sql3 = "SELECT * , Count( picklist.Initial ) AS CInitial
FROM picklist
LEFT JOIN warehouseops ON picklist.Initial = warehouseops.Initial
WHERE PDate >= '$Today' ' 16:00:00'
AND PDate < '$Today' ' 23:59:00'
GROUP BY picklist.Initial
ORDER BY CInitial DESC LIMIT 0 , 30
";