The dates stored in the database are like 3/1/2021 as smalldatetime
But I'm asking to compare against 2021-03-01, an ISO date, so wrap what I'm asking to compare to.
I came up with this ...
$query = "
FROM project as a, commission_summary as b
WHERE a.project_no = b.project_no
AND (a.sold_date <= '$lastDate')
AND (a.status = 'construction' or a.status = 'finished')
AND (convert(CHAR(10), b.startup_check_date, 120) BETWEEN '$firstDate' AND '$lastDate')
AND a.sales_no = '$salesId'";
I fiddled with the conversion, and was sure I got it right but it failed. Then I used the suggestion to wrap the dates in single quotes and it produced a clean result of 9.
I get the convert part, declare a CHAR no more than 10, input value, ?
Not sure what the 120 stands for.
However your explanation of the date formats has schooled me in how to fiddle with them and get it right.
Just FYI, I wrote some other fixes earlier that are similar, and just had a complete blackout of my previous experiences. But this lesson should solidify it. Thanks!
If it ain't broke don't fix it
Discover my world at jkirkerx.com