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

    Join Date
    Sep 2013
    Posts
    6
    Rep Power
    0

    Help with a quick table query


    Hi All,

    I have 'inherited' a project where the database / dates are a bit screwed up.

    I have been handed a simple table with the column titles...

    RecordId
    InputDate
    MySqlDate (which i added)

    The id is the id, the InputDate unfortunately is a tinytext where the input format is for example...

    20/08/2013

    or

    20/8/2013

    I have added the MySqlDate as a date column to make everything easier for new entries / queries and all is good.

    Now I need to convert / update the existing data from the InputDate column to the respective MySqlDate column.

    i.e. from tinytext to date - 0000-00-00

    Got me stumped.

    Any tips appreciated.

    Rik
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    the problem you're going to face is that if you can have both 08/20 and 20/08 in the text column, then how the heck are you going to decide whether 04/03 is april 3rd or march 4th?

    once you can answer that, the conversion update should be easy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    65
    Rep Power
    3
    the dates in the example are not like that. they are both in European format with the date first and the month second.
    you should be able to update the MySqlDate column using the input date, just parse it by the '/'
    something like :
    SUBSTRING_INDEX(inputdate,'/',1) for the Date

    you may have to do some casting to get it to be a real number
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by bobert123
    the dates in the example are not like that.
    oh, that's right, i just noticed

    okay, STR_TO_DATE will do the job nicely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo