Filter MySQL Query By DateTime Stored As Varchar
Your use of STR_TO_DATE
is correct, but your date literals are off. Fix them, and your query should work:
SELECT *
FROM table_name
WHERE STR_TO_DATE(datetime,'%H:%i:%S %m/%d/%y') >= '2021-01-14' AND
STR_TO_DATE(datetime,'%H:%i:%S %m/%d/%y') < '2021-01-15';
The above assumes that you want records occurring on 14th January, 2021, proper. Note that we don't need to even include the H:M:S components and can just use date literals.
It would be best to make your datetime
column a proper datetime type column, rather than text. This would avoid the need to use STR_TO_DATE
.
mysql: searching BETWEEN dates stored as varchar
You want to search between dates, store them as dates. By storing them as strings you're shooting yourself in the foot.
You'd basically need to extract date part from the string (using SUBSTR()
or LEFT()
) and parse it to date format (using STR_TO_DATE()
).
The performance of such solution will be appaling.
STR_TO_DATE(LEFT(reporttime,LOCATE(' ',reporttime)),'%m/%d/%Y') BETWEEN '2010-07-28' AND '2010-07-29'
Search between dates (date as VARCHAR)
It is not returning any rows because the query is not doing the comparison on valid DATE
or DATETIME
fields. You should be storing the dates as a DATE
type, but what you could do is this:
// Switch the order of date elements:
$vanaf = mysql_real_escape_string("$fromyear-$frommonth-$fromday");
$tot = mysql_real_escape_string("$tillyear-$tillmonth-$tillday");
$sel = "SELECT * FROM bestelling WHERE STR_TO_DATE(verzenddatum, '%d-%m-%Y') BETWEEN '$vanaf' AND 'tot'";
The mysql_real_escape_string()
function simply reduces the risk of SQL injection, which is what your original code was vulnerable to.
The MySQL function STR_TO_DATE()
converts a string to a valid MySQLDATE
type. %d-%m-%Y
is the format you have in your varchar string currently, but STR_TO_DATE
converts it to '%Y-%m-%d'
which MySQL can then use to make range comparisons.
Also, I'm using the BETWEEN
syntax in the SQL as it is the same thing as val >= val1 AND val <= val2
. It's just clearer and simpler: val BETWEEN val1 AND val2
.
How to search a range of dates stored as a varchar?
You can store them as separate dates like so:
CREATE TABLE
`dates`
(
`date_id` INT PRIMARY KEY AUTO_INCREMENT,
`start_date` DATE,
`end_date` DATE
);
INSERT INTO
`dates`
(`start_date`, `end_date`)
VALUES
('2017-12-13', '2017-12-25');
Then if you want to query based on the range, you could do something like:
SELECT
DATE_FORMAT(`start_date`, '%M %d, %Y') AS `start_date`,
DATE_FORMAT(`end_date`, '%M %d, %Y') AS `end_date`
FROM
`dates`
WHERE
`start_date` BETWEEN '2017-01-01' AND '2017-12-31';
Which provides:
start_date | end_date
-------------------------------------------
December 13, 2017 | December 25, 2017
Now... to get there, you have to parse the existing records, so assuming a table:
CREATE TABLE
`fixme`
(
`date_id` VARCHAR(50)
);
INSERT INTO
`fixme`
VALUES
('December 13, 2017 - December 25, 2017');
You can split them into dates and insert them into the new table:
SELECT
STR_TO_DATE(SUBSTRING_INDEX(`date_id`, ' - ', 1),'%M %d, %Y') AS `start_date`,
STR_TO_DATE(SUBSTRING(`date_id`, LOCATE(' - ', `date_id`)+2),'%M %d, %Y') AS `end_date`
FROM
`fixme`;
Results in:
start_date | end_date
----------------------
2017-12-13 | 2017-12-25
SQL Fiddle: http://sqlfiddle.com/#!9/4101cd/7
How to search a range of dates stored as a varchar?
You can store them as separate dates like so:
CREATE TABLE
`dates`
(
`date_id` INT PRIMARY KEY AUTO_INCREMENT,
`start_date` DATE,
`end_date` DATE
);
INSERT INTO
`dates`
(`start_date`, `end_date`)
VALUES
('2017-12-13', '2017-12-25');
Then if you want to query based on the range, you could do something like:
SELECT
DATE_FORMAT(`start_date`, '%M %d, %Y') AS `start_date`,
DATE_FORMAT(`end_date`, '%M %d, %Y') AS `end_date`
FROM
`dates`
WHERE
`start_date` BETWEEN '2017-01-01' AND '2017-12-31';
Which provides:
start_date | end_date
-------------------------------------------
December 13, 2017 | December 25, 2017
Now... to get there, you have to parse the existing records, so assuming a table:
CREATE TABLE
`fixme`
(
`date_id` VARCHAR(50)
);
INSERT INTO
`fixme`
VALUES
('December 13, 2017 - December 25, 2017');
You can split them into dates and insert them into the new table:
SELECT
STR_TO_DATE(SUBSTRING_INDEX(`date_id`, ' - ', 1),'%M %d, %Y') AS `start_date`,
STR_TO_DATE(SUBSTRING(`date_id`, LOCATE(' - ', `date_id`)+2),'%M %d, %Y') AS `end_date`
FROM
`fixme`;
Results in:
start_date | end_date
----------------------
2017-12-13 | 2017-12-25
SQL Fiddle: http://sqlfiddle.com/#!9/4101cd/7
Filter dates stored as varchar in mysql
Storing a datettime value in a varchar column complicates some functionality on date time operations. But of course you can select your values writing such a query as follow
SELECT * FROM table_name WHERE cl_223 LIKE '2013%'
But if you don't have any performance issue you can convert the varchar column to a datetime value and write stronger typed query like this:
SELECT * FROM table_name WHERE STR_TO_DATE(cl_223,'%Y-%m-%d2') BETWEEN '2013-01-01' AND '2013-12-31'
But if you need a date time value as a date time in your process you'd better store it in a datetime column instead of a varchar column.
Related Topics
Using Insert into with 'Select' to Supply Some Values But Not Others (Access 2010)
Join/Pivot Items with Eav Table
Calling a Stored Procedure Within a Stored Procedure
Connect to Remote SQL Database Using Excel
Why Isn't Postgres Using the Index
Passing Array of a Composite Type to Stored Procedure
Case Statement with Different Data Type
How to Select Records That Don't Exist in SQL Server
Aggregate String Concatenation in Oracle 10G
Unpivot on an Indeterminate Number of Columns
Comma-Separated String into Table's Column in SQL Server
Interval (Days) in Postgresql with Two Parameters
Local Collection Types Not Allowed in SQL Statements
Oracle as Keyword and Subqueries
How to Extract Multiple Strings from Single Rows in SQL Server
Good Database and Structure to Store Synonyms