Convert varchar to date

oldnickj · #1 (**permalink**) 02-09-09, 17:56

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!

gvee · #2 (**permalink**) 02-09-09, 18:05

03/00/2009 is not a valid date...
3rd Jan 09?
1st Mar 09?

oldnickj · #3 (**permalink**) 02-09-09, 19:24

How about 2009-02-04 18:57:40 a standard formated mysql date?

r937 · #4 (**permalink**) 02-09-09, 22:47

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'))

oldnickj · #5 (**permalink**) 02-11-09, 01:39

Cool I'll try it!

Thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech