[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