MySQL Forums
Forum List  »  Newbie

Re: New to SQL Query
Posted by: Peter Brawley
Date: August 19, 2015 01:16PM

> "4/8/2015 5:09:57PM"

Then it's a (var)char column, not a datetime column. MySQL stores dates as yyyy-mm-dd, so you can't count on values in your column following a consistent format. The the best solution would be to convert the column, eg assuming a table named tbl with a column named `c` containing your pseudodates ...

alter table tbl add column dt datetime not null;
update tbl set dt = str_to_date( c, "%m/%d/%Y %r" );
-- check values, fix whatever needs fixing ...
select c,dt from tbl;
-- when you're satisfied ...
alter table tbl drop column c;

Options: ReplyQuote


Subject
Written By
Posted
August 19, 2015 12:01PM
Re: New to SQL Query
August 19, 2015 01:16PM
August 19, 2015 01:42PM
August 19, 2015 07:04PM
August 21, 2015 10:41AM
August 21, 2015 12:58PM
August 24, 2015 02:53PM
August 20, 2015 05:30AM
August 21, 2015 10:44AM
August 25, 2015 05:09AM
August 21, 2015 11:27AM
August 22, 2015 03:50PM
August 24, 2015 02:53PM
August 24, 2015 05:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.