MySQL – How to Change All VARCHAR Date Formats with a Single Query

date formatMySQL

|date(varchar) |
|--------------|
|2016-02-16    |
| -------------|
|03-12-2015    |
|--------------|
|07-07-2007    |
|--------------|
|2016-03-14    |

Dates are stored in different formats and type is varchar. How to change all formats with single query in MySQL?

The formats are d/m/y, d-m-y, and y/m/d.

I need to copy these to a new column in date format y-m-d

Best Answer

Create the new column as type DATE.

Then run 3 queries, one for each conversion needed. Sure, they could be combined into a single query, but why bother; it just makes it more complex and likely to break.

And finally drop the old column and rename the new on into place?

The queries would be something like:

ALTER TABLE add COLUMN new_date DATE;
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d-%m-%Y') WHERE substring(date,3,1) = '-';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%d/%m/%Y') WHERE substring(date,3,1) = '/';
UPDATE tbl SET new_date = STR_TO_DATE(date,'%Y/%m/%d') WHERE substring(date,5,1) = '-';

Then do a SELECT to see if any rows still have a DEFAULT value for new_date;

Related Solutions

Oracle 12c – Change Date Precision Values

I have an oracle database table with date format as 'mm/dd/yyyy'

No you don't.. ;)

You have an Oracle database table with a date datatype. Period.

Any type of "format" is for display only, and is easily modified.

  SQL> create table junk ( id number,  dd  date );

  Table created.

  SQL> insert into junk values ( 1, sysdate );

  1 row created.

  SQL> commit;

  Commit complete.

  SQL> select * from junk;

          ID DD
  ---------- ---------
           1 31-MAY-16

  SQL>
  SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss'
    2  /

  Session altered.

  SQL> select * from junk;

          ID DD
  ---------- --------------------
           1 31-may-2016 11:17:44

  SQL>

An Oracle DATE datatype holds Year + Month + Day + Hours + Minutes + Seconds information. It's up to you how to display/use it.

Note that an Oracle TIMESTAMP datatype holds milliseconds in addition to what a DATE holds.

It would be much easier to give you a more specific answer if you'd actually show your table definition, and your query and explain the problem you're having, and what you want to see.

SQL Server Date Format – Why Update Statements Fail with Datetime

You could deal with the issue by specifying the style and doing an explicit CONVERT() from string to datetime rather than an implicit conversion. https://msdn.microsoft.com/en-us/library/ms187928.aspx

UPDATE dbo.t SET mydatetime = CONVERT('2011-12-25 07:00:00.000', 121)

Best Answer

Related Solutions

Oracle 12c – Change Date Precision Values

SQL Server Date Format – Why Update Statements Fail with Datetime

Related Question