All,
I have a MYSQL table with a column called timestamp. It is of DATETIME
datatype and has values like "10/1/2009 3:25:08 PM', "10/1/2009 3:30:05 PM', "10/4/2009 3:40:01 PM', etc..
I want to write a SQL query to select all the values in the timestamp field occuring between two dates.. something like this:
select timestamp from tablename where timestamp >= userStartDate and timestamp <= userEndDate
The userInput dates will not have time portions. Can you please suggest the correct MySQL Query Syntax for this? Thanks
SELECT timestamp
FROM tablename
WHERE timestamp >= userStartDate
AND timestamp < userEndDate + INTERVAL 1 DAY
This will select every record having date portion between userStartDate
and userEndDate
, provided that these fields have type of DATE
(without time portion).
If the start and end dates come as strings, use STR_TO_DATE
to convert from any given format:
SELECT timestamp
FROM tablename
WHERE timestamp >= STR_TO_DATE('01/11/2010', '%m/%d/%Y')
AND timestamp < STR_TO_DATE('01/12/2010', '%m/%d/%Y') + INTERVAL 1 DAY
SELECT timestamp
FROM myTable
WHERE timestamp BETWEEN startDate AND endDate
For best results when using
BETWEEN
with date or time values, you should useCAST()
to explicitly convert the values to the desired data type. Examples: If you compare aDATETIME
to twoDATE
values, convert theDATE
values toDATETIME
values. If you use a string constant such as'2001-1-1'
in a comparison to aDATE
, cast the string to aDATE
.
-
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_between
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With