Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Wink Unanswered: Convert varchar to date

    In an inherited database I have a column of dates in varchar (5) i.e. 3/09 which I would like to convert to a date field. i.e 03/00/2009!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    03/00/2009 is not a valid date...
    3rd Jan 09?
    1st Mar 09?
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    How about 2009-02-04 18:57:40 a standard formated mysql date?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by oldnickj
    In an inherited database I have a column of dates in varchar (5) i.e. 3/09 which I would like to convert to a date field. i.e 03/00/2009!
    your best bet is to chose the 1st of the month

    e.g.
    3/09 --> 2009-03-01
    9/09 --> 2009-09-01
    12/09 --> 2009-12-01

    if this works for you, then use DATE(CONCAT('20',RIGHT(yourfld,2),'-',SUBSTRING_INDEX(yourfld,'/',1),'-01'))

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2009
    Posts
    124
    Cool I'll try it!

    Thanks

Posting Permissions

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