Start dates are stored in a comma-separated string in MySQL. I want a stored function to get the earliest start date from the CSV string
EG:
The string: 2018-04-16,2018-10-08,2018-11-15
The desired result: 2018-04-16
-
Please post the code for your stored function so we can help you fix the issue.Grokify– Grokify2018-06-11 02:47:07 +00:00Commented Jun 11, 2018 at 2:47
-
I'm thinking something like this:Nugget– Nugget2018-06-12 05:14:51 +00:00Commented Jun 12, 2018 at 5:14
-
CREATE FUNCTION MIN_DATE_FROM_CSV(str VARCHAR(255)) RETURNS VARCHAR(255) BEGIN DECLARE mindate VARCHAR(12) SET mindate = 0 //get first date (up to first comma) mindate = SUBSTRING('str',0,LOCATE(',',str)) //the remainder of str(without the first comma) str = SUBSTRING('str',(LOCATE(',',str)+1)) WHILE LENGTH(str) > 0 //get first date (up to first comma) x = SUBSTRING('str',0,LOCATE(',',str)) IF x <= mindate THEN mindate = x //the remainder of str(without the comma) str = SUBSTRING('str',(LOCATE(',',str)+1)) END IF END WHILE RETURN mindate END $$Nugget– Nugget2018-06-12 05:15:59 +00:00Commented Jun 12, 2018 at 5:15
Add a comment
|
2 Answers
Assuming your date strings always have exactly three dates, then the following should work:
SELECT
LEAST(SUBSTRING_INDEX(dates, ',', 1),
SUBSTRING_INDEX(SUBSTRING_INDEX(dates, ',', -2), ',', 1),
SUBSTRING_INDEX(dates, ',', -1)) AS least_date
FROM yourTable;
2018-04-16
Demo
However, your current table has seriously poor design if you are storing dates as CSV strings. Instead, you should be storing each date in a separate record. Then, you could easily use the MIN function, possibly with GROUP BY, to get the answer you need.
3 Comments
Nugget
Thanks for your response. No there won't always be 3 dates. That's why I thought a stored function might provide a solution. I agree about the bad design but sometimes you just have to work with what you are given.
Tim Biegeleisen
I don't see any point in writing a proc. Just fix your data model. Anyway, all operations will be difficult, including insertion and especially updating.
Nugget
Tim, if I can get this to work, It will save a lot of time. I'm most of the way there - just not sure of the MYSql syntax
This is close. - still some syntax errors:
CREATE FUNCTION min_date_from_csv(str VARCHAR(255))
RETURNS VARCHAR(12)
BEGIN
DECLARE mindate VARCHAR(12);
DECLARE x VARCHAR(12);
SET mindate = 0;
SET x = 0;
IF LOCATE(',',str)>0 THEN
mindate = SUBSTRING(str,0,(LOCATE(',',str)));
str = SUBSTRING(str,(LOCATE(',',str)));
TRIM(LEADING ',' FROM str);
ELSE
mindate = str;
END IF;
WHILE LOCATE(',',str)>0
x = SUBSTRING(str,0,(LOCATE(',',str)));
IF x <= mindate THEN
mindate = x;
END IF;
str = SUBSTRING(str,(LOCATE(',',str)));
TRIM(LEADING ',' FROM str);
END WHILE;
RETURN mindate;