I have a table with some datetime columns. I need to be able to select the rows between two datetimes on the same column.
For example:
SELECT *
FROM `t_punchcards`
WHERE UserID = 'root' AND
PunchInTime > CAST('01-01-2015 00:00:00' AS DATETIME) AND
PunchInTime < CAST('03-06-2015 23:59:59' AS DATETIME);
For some strange reason, this yields no results. I have tried using BETWEEN, I've tried not casting to datetime from string (just use a raw string), I've tried <= >=. I'm very lost.
As a sidenote, removing the second constraint (AND PunchInTime < CAST('03-06-2015 23:59:59' AS DATETIME); WILL in fact yield results. Are you not allowed to do a comparison against the same column in the same query?
Here's an example of the table:
UserID(varchar) PunchInTime(datetime) PunchOutTime(datetime)
root 01-01-2015 8:02:31 01-01-2015 12:35:51
Just write this logic as:
WHERE UserID = 'root' AND
PunchInTime > DATE('2015-01-01') AND
PunchInTime < DATE('2015-03-07')
Note that I removed the time component from the second value. This is a cleaner way of making the comparison, unless you really want to treat the last few milliseconds of a day differently from the rest of the time during the day.
Note: If you are not using MySQL or you want more compliant code, the following works in more databases:
WHERE UserID = 'root' AND
PunchInTime > CAST('2015-01-01' as DATETIME) AND
PunchInTime < CAST('2015-03-07' as DATETIME)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments