#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    266
    Rep Power
    11

    Help with convert a string to date


    2 years ago I was a very beginner learning with mysql and php and I created my first MySQL database and I made a huge mistake and now I am regret.

    That mistake was I created a table that has a date column but I created that column as a Text data type and that column now has over 2000 records in format like this "01:13:2006" and the column name is regdate

    Now, I am having problem with this date column and I would like to convert this column into a date datatype with its format like "01/13/2006"

    I tried this statement
    PHP Code:
    UPDATE mytable SET newdate =  STR_TO_DATE(oldate'%m/%d/%y'
    but it does not do any update.

    Can you help me?
    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by chichai
    ...format like this "01:13:2006" and the column name is regdate
    you were close
    Code:
    UPDATE mytable SET newdate =  STR_TO_DATE(regdate, '%m:%d:%Y')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    266
    Rep Power
    11
    Originally Posted by r937
    you were close
    Code:
    UPDATE mytable SET newdate =  STR_TO_DATE(regdate, '%m:%d:%Y')
    So I can't use this format %m/%d/%Y?

    Could you please show me if after I converted to this format %m:%d:%Y then to this %m/%d/%Y?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    you need to understand the difference between a DATE datatype, and the string representation of a date

    once you have created your DATE column values, you can display them in whatever format you want, using the DATE_FORMAT function if you want to do it in the sql

    however, best practice says to retrieve the DATE untouched, and do any custom formatting in the application language (php or whatever)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    266
    Rep Power
    11
    Originally Posted by r937
    you need to understand the difference between a DATE datatype, and the string representation of a date

    once you have created your DATE column values, you can display them in whatever format you want, using the DATE_FORMAT function if you want to do it in the sql

    however, best practice says to retrieve the DATE untouched, and do any custom formatting in the application language (php or whatever)
    Ah, got it.

    Thanks so much

IMN logo majestic logo threadwatch logo seochat tools logo