SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    varchar(50) to timestamp(14)

    Is there an easy way to convert a field1(varchar(50)) to field2(timestamp(14))?

    greetz

    Avido

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    easy? you decide

    if the dates are in yyyy-mm-dd format, you are half way home

    by comparison, here's what you would have to do to convert dates that are written in [d]d-[m]m-yyyy format --
    PHP Code:
    insert into alphanumerics (alphanumvalues
     
    ('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)

    rudy
    http://rudy.ca/

  3. #3
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    :-) hm, easy huh...
    well, received an easier solution, I have to try it first to see if it is as easy as it looks.
    If not, i'll use your solution, well, surely give it try at least.

    thx for the reply.

    Avido


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •