Mysql: Searching Between Dates Stored as Varchar

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