[thelist] MySQL Convert String To Date
rudy
rudy937 at rogers.com
Tue Jul 22 18:42:41 CDT 2003
> The dates are in the format d[d]/m[m]/yyyy.
> Can I convert such a string to a date that I can
> perform calculations on within SQL using MySQL?
yes, but you have to unstring/restring it yourself first
in order to get it into acceptable year-month-day format
"Although MySQL tries to interpret values in several
formats, it always expects the year part of date values
to be leftmost. Dates must be given in year-month-day order
(for example, '98-09-04'), rather than in the month-day-year
or day-month-year orders commonly used elsewhere (for example,
'09-04-98', '04-09-98')"
http://www.mysql.com/doc/en/Date_and_time_types.html
here's my solution, feel free to improvise --
insert into alphanumerics (alphanum) values
('2/2/2'),('9/9/1949'),('09/09/49'),('3/4/05'),('4/3/04')
select alphanum
, left(alphanum,locate('/',alphanum,1)-1) as D
, mid(alphanum,locate('/',alphanum,1)+1
,locate('/',alphanum,4)
-locate('/',alphanum,1)-1) as M
, mid(alphanum,locate('/',alphanum,4)+1,4) as Y
, concat(
mid(alphanum,locate('/',alphanum,4)+1,4)
,'/'
,mid(alphanum,locate('/',alphanum,1)+1
,locate('/',alphanum,4)
-locate('/',alphanum,1)-1)
,'/'
,left(alphanum,locate('/',alphanum,1)-1)
) as YMD
, cast(
concat(
mid(alphanum,locate('/',alphanum,4)+1,4)
,'/'
,mid(alphanum,locate('/',alphanum,1)+1
,locate('/',alphanum,4)
-locate('/',alphanum,1)-1)
,'/'
,left(alphanum,locate('/',alphanum,1)-1)
) as datetime) as YMDdate
, cast(
concat(
mid(alphanum,locate('/',alphanum,4)+1,4)
,'/'
,mid(alphanum,locate('/',alphanum,1)+1
,locate('/',alphanum,4)
-locate('/',alphanum,1)-1)
,'/'
,left(alphanum,locate('/',alphanum,1)-1)
) as datetime)
+ interval 1 day as YMDdateplusone
from alphanumerics
alphanum D M Y YMD YMDdate YMDdateplusone
2/2/2 2 2 2 2/2/2 2/2/2 2002-02-03
9/9/1949 9 9 1949 1949/9/9 1949/9/9 1949-09-10
09/09/49 09 09 49 49/09/09 49/09/09 2049-09-10
3/4/05 3 4 05 05/4/3 05/4/3 2005-04-04
4/3/04 4 3 04 04/3/4 04/3/4 2004-03-05
see, it works ;o)
now, what did you say was holding you up from declaring a proper datetime
datatype?
rudy
More information about the thelist
mailing list