How to search a range of dates stored as a varchar?

Question

I have a primary key for a table "December 13, 2017 - December 25, 2017" which is stored as a varchar.

What do I query so that I get a list of results from a search entry like January - December 2017?

You don’t; you fix this absolutely terrible data model first. — CBroe, Dec 01 '17 at 15:25
You make two new columns as datetimes. In one store the start time, in the other the end time and then search the dates — Peon, Dec 01 '17 at 15:25
Wow, this is unexpected. Composite primary key as 2 dates in textual form. I will bookmark it :) — Lukasz Szozda, Dec 01 '17 at 15:26
I seriously am having a hard time during the searching. I don't know what to do especially that the primary key needs to be in range. — John Valle, Dec 01 '17 at 15:30
Sorry didn't go to well on your first question John. But in this case you are trying to treat the symptoms instead of cure the disease. Fix your db model and store the dates as `DATE`, you can use `STR_TO_DATE()` function https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql — Juan Carlos Oropeza, Dec 01 '17 at 15:32
@chris85 Will a column with a datetime datatype store a range of dates? — John Valle, Dec 01 '17 at 15:32

score 1 · Answer 1 · answered Dec 01 '17 at 15:46

1

Convert your varchar into 2 columns like startDate and endDate in your mysql table.

var str = "December 13, 2017 - December 25, 2017"; var arr = str.split('-');

var startDate = new Date(arr[0]); var endDate = new Date(arr[1]);

If you store these into your database, it is easy to write queries in sql.

answered Dec 01 '17 at 15:46

Wilson

329
2
11

How do I convert? Do I add 2 new columns? – John Valle Dec 01 '17 at 15:50
Yes. Alter the table and add 2 columns like startDate and endDate. You can insert values to those 2 columns through program. I have shared the JavaScript program to do it. – Wilson Dec 04 '17 at 10:16

score 0 · Accepted Answer · answered Dec 01 '17 at 15:48

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?

2 Answers2