CODEWITHSUNDEEP
mysqldatabasedateformattingdate-format
Franco
Franco

Reputation: 2926

mySQL - How to change date format from dd-mm-yy to yy-mm-dd?

I have some dates in my DB in the format of dd-mm-yy and some in yy-mm-dd

I want them all to be in yy-mm-dd

They are stored in VARCHAR fields

Examples dates are:

2011-10-19 and 19-10-2011

Is there a query to run to update to any that are not in this format?

Thanks in advance!

Upvotes: 1

Views: 2314

Answers (2)

Michael Durrant
Michael Durrant

Reputation: 96484

Dates are stored as dates, presumably you have strings.

If they are already actually dates just use DATE_FORMAT as needed.

If they are strings, your best option would be to change them to actual dates and store the date value in a new column. Otherwise, down the road you will continue to have a lot of issues to deal with as Tony noted (+1 Tony!).

You can then populate it with STR_TO_DATE, e.g. 

update tbl set new_date_field =
select STR_TO_DATE(string_date_field,'%y-%m-%d') ` # if string_date_field is '2011-01-01'
WHERE string_date_field RLIKE "^[0-9]{2}-";

Once you have a real date you can display it with DATE_FORMAT(date_field, '%y-%m-%d')

The only other option if you can't add the field is to do string manipulation, i.e.
Update table set date_field =
substr(string_date_field,7,2) + '-' + substr(string_date_field,3,2) + '-' + substr(string_date_field,0,2) WHERE string_date_field RLIKE "^[0-9]{2}-";
even this way I would still recommend a new field.

Note: if you need to do it in even more steps, say you can't change the population of the 'date as string' field immediately you could also put in a post insert / post update trigger (which mySQL now supports) to do the conversion (from the string field to the real date field) going forward.

Upvotes: 3

georgepsarakis
georgepsarakis

Reputation: 1957

You could try something like this (but first backup your table!):

UPDATE table SET string_date_field = STR_TO_DATE(string_date_field, "%d-%m-%Y")
WHERE string_date_field RLIKE "^[0-9]{2}-";

RLIKE is a synonym of REGEXP and you can use to find the records where the date is in the problematic %d-%m-%Y format. You can afterwards ALTER the table so that the field type is DATE. Also, if your table has a large number of records, maybe you should do this in intervals using a continuous auto-increment field (provided that one exists of course!) to create smaller sets of records for the server to process. For example:

UPDATE table SET string_date_field = STR_TO_DATE(string_date_field, "%d-%m-%Y")
WHERE ID >= 1000000 AND ID < 2000000 AND string_date_field RLIKE "^[0-9]{2}-";

Upvotes: 2

Related Questions