[Resolved] using SUBSTRING

gmp

i have a date stored (incorrectly) as a string in this format: mm/dd/yyyy. i've been able, with the help of some people here, to compare that string to another date object using SUBSTRING as follows:

(SUBSTRING(record_date,7,4) BETWEEN '$startyear' AND '$endyear') 
AND (SUBSTRING(record_date,1,2) BETWEEN '$startmonth' AND '$endmonth') 
AND (SUBSTRING(record_date,4,2) BETWEEN '$startdate' AND '$enddate')

however, i'm now realizing that not only is the date stored improperly as a string, but it is not written with leading zeroes. so i have both 11/17/2003 and 1/1/2003 in my table. obviously, 11/17/2003 fits my SUBSTRING pattern while 1/1/2003 does not.

i wasn't able to find anything in the mySQL manual that would help me here -- is there an SQL function similar to PHP's explode() that i could use to split this date string at the slashes, regardless of the number of digits?

thanks for your help.

mtombs

Looks like your in trouble there. If I were you I would write a script to convert those strings into proper dates. If you can't do that, the only thing I can think of is to do a load of OR statements, for example:

((SUBSTRING(record_date,7,4) BETWEEN '$startyear' AND '$endyear') 
OR
(SUBSTRING(record_date,5,4) BETWEEN '$startyear' AND '$endyear') 
OR
(SUBSTRING(record_date,6,4) BETWEEN '$startyear' AND '$endyear') 
)
AND
( (SUBSTRING(record_date,1,2) BETWEEN '$startmonth' AND '$endmonth')  OR ...)
AND ((SUBSTRING(record_date,4,2) BETWEEN '$startdate' AND '$enddate') OR ...)

Note the brackets. But that is really horrible. Or I suppose you could get the whole dataset and get your PHP script to filter it, but thats pretty horrible too. Good luck.

gmp

Originally posted by mtombs
Looks like your in trouble there.

i was afraid of that. i guess i'll write something to update each record with a proper date string. hopefully that will prevent other problems like this from popping up in the future.

thanks for your help!

LordShryku

Have you looked at trying to use maybe DATE_FORMAT and CAST?

gmp

Originally posted by LordShryku
Have you looked at trying to use maybe DATE_FORMAT and CAST?

i did try CAST and STR_TO_DATE, but with no luck -- possibly i did something wrong, possibly my server isn't running the most up-to-date version of mySQL.

but i did some further reading, and i've been having some luck using SUBSTRING_INDEX. if my format is m[m]/d[d]/yyyy, i can nest a query as follows:

SUBSTRING_INDEX(record_date, '/', -1) = yyyy
SUBSTRING_INDEX(record_date, '/', 2) = m[m]/d[d]
SUBSTRING_INDEX(SUBSTRING_INDEX(record_date, '/', 2), '/', 1) = m[m]
SUBSTRING_INDEX(SUBSTRING_INDEX(record_date, '/', 2), '/', -1) = d[d]

still having some problems, but i think i'm getting there.

LordShryku

A small script could probably update this all to regular dates for you. Then you could change the field type to "date", and stop worrying about this. Something like this...

<?php
$conn = mysql_connect("","","");
$db   = mysql_select_db("", $conn);
$sql  = "SELECT unique_id, 
                date
         FROM   table";
$res  = mysql_query($sql);
while($row=mysql_fetch_array($res)) {
   mysql_query("UPDATE table
                SET    date = '".date("Y-m-d", strtotime($row['date']))."'
                WHERE  unique_id = '".$row['unique_id']."'");
}
mysql_close($conn);
?>

gmp

Originally posted by LordShryku
A small script could probably update this all to regular dates for you. Then you could change the field type to "date", and stop worrying about this.

yep, that did the trick... many thanks!