Mysql Convert Date from Mm-Dd-Yyyy to Yyyy-Mm-Dd Format

Converting varchar mm/dd/yy to date format yyyy-mm-dd

You can try the STR_TO_DATE to convert a string to a date:

SELECT STR_TO_DATE(datecreated,'%m/%d/%Y') as date
FROM employee

Date format specifications (%m, etc) can be found here.

mysql convert date from mm-dd-yyyy to YYYY-MM-DD format

Use STR_TO_DATE function to convert your string date to date type. For example:

SELECT STR_TO_DATE("09-02-2018", '%d-%m-%Y')

Conversion of Mysql date format (DD-MM-YYYY TO YYYY-MM-DD)

You can use the STR_TO_DATE function to convert the string to a Date value. You have to specify the format of the date string you provided. In your case it would be '%d-%m-%Y'. For testing purposes you can run the following queries to check if it works:

mysql> EXPLAIN Dummy;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ID | int(11) | YES | | NULL | |
| oldDate | varchar(50) | YES | | NULL | |
| logdate | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM Dummy;
+------+------------+------------+
| ID | oldDate | logdate |
+------+------------+------------+
| 1 | 24-12-2019 | 2000-01-01 |
| 1 | 04-09-2017 | 2000-01-01 |
| 1 | 21-02-2019 | 2000-01-01 |
+------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT oldDate, STR_TO_DATE(oldDate, '%d-%m-%Y') FROM Dummy;
+------------+----------------------------------+
| oldDate | STR_TO_DATE(oldDate, '%d-%m-%Y') |
+------------+----------------------------------+
| 24-12-2019 | 2019-12-24 |
| 04-09-2017 | 2017-09-04 |
| 21-02-2019 | 2019-02-21 |
+------------+----------------------------------+
3 rows in set (0.00 sec)

You can then run an UPDATE query to update the values in the logdate column.

mysql> UPDATE Dummy SET logdate = STR_TO_DATE(oldDate, '%d-%m-%Y');
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0

mysql> SELECT * FROM Dummy;
+------+------------+------------+
| ID | oldDate | logdate |
+------+------------+------------+
| 1 | 24-12-2019 | 2019-12-24 |
| 1 | 04-09-2017 | 2017-09-04 |
| 1 | 21-02-2019 | 2019-02-21 |
+------+------------+------------+
3 rows in set (0.00 sec)

SQL date format convert? [dd.mm.yy to YYYY-MM-DD]

Since your input is a string in the form 03.09.13, I'll assume (since today is September 3, 2013) that it's dd.mm.yy. You can convert it to a date using STR_TO_DATE:

STR_TO_DATE(myVal, '%d.%m.%y')

Then you can format it back to a string using DATE_FORMAT:

DATE_FORMAT(STR_TO_DATE(myVal, '%d.%m.%y'), '%Y-%m-%d')

Note that the year is %y (lowercase "y") in STR_TO_DATE and %Y (uppercase "Y") in DATE_FORMAT. The lowercase version is for two-digit years and the uppercase is for four-digit years.

Convert date format from dd-MM-yyyy to yyyy-mm-dd - in mysql

UPDATE test
SET new_date = CASE WHEN old_date REGEXP '\\d{4}-\\d{2}-\\d{2}' -- test for YYYY-MM-DD
THEN old_date
WHEN old_date REGEXP '\\d{1,4}-\\w{3}-\\d{4}' -- test for DD-MMM-YYYY
THEN STR_TO_DATE(old_date, '%d-%b-%Y')
ELSE NULL -- unknown format
END
WHERE new_date IS NULL -- do not update existing values
;

fiddle



this mysql code supports on only mysl 8.0 and not 5.7 – user14812075

For 5.7 - use class specification instead of \d and \w tokens:

UPDATE test
SET new_date = CASE WHEN old_date REGEXP '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}'
THEN old_date
WHEN old_date REGEXP '[[:digit:]]{1,4}-[[:alpha:]]{3}-[[:digit:]]{4}'
THEN STR_TO_DATE(old_date, '%d-%b-%Y')
ELSE NULL
END
WHERE new_date IS NULL;

fiddle

Date format conversion `dd-mm-yyyy` to `yyyy-mm-dd` from in mysql procedure

The lowercase version ('y') is for two-digit years and the uppercase ('Y') is for four-digit years. https://www.w3resource.com/mysql/date-and-time-functions/mysql-date_format-function.php

So try this:

select str_to_date('01-01-87','%d-%m-%Y')

How can I convert date format from YYYY-MM-DD to YYYY-MM in sql

Assuming that you have a date datatype or the-like (datetime, timestamp), you can use date_format() to represent your date in the target format:

date_format(mydate, '%Y-%m')

This returns a string in the target format. It does not make sense to convert your date column to a string though. Keep that column as it is, and maybe use a computed column to automatically derived the string representation you want:

create table mytable (
...
mydate date,
mynewcol varchar(7) as (date_format(mydate, '%Y-%m'))
)

How to convert date from MM/dd/yyyy to yyyy-MM-dd in MySQL? Error Code: 1411. Incorrect datetime value: '' for function str_to_date

My apologies if you have already taken the following things into consideration but I thought them worth mentioning.

Given that you say this is a "large dataset" I assume this is a table that is currently in use. Does the existing application rely on the Actual_Date being in that string format? Does it rely on a fixed number of columns in the table? Some poorly written applications can be very brittle when it comes to changing underlying data structure.

You may want to consider creating a copy of the current table, modifying the structure of the copy, and replacing the original with a view with the same columns and formats as the original. This way you get improved data but reduce risk to existing application.

In the title and first line of your question you state that the current format is MM/dd/yyyy

Update human_resources.timekeeping Set Actual_Date = str_to_date(Actual_Date,'%m/%d/%Y');

How to convert mixed dates in MYSQL to yyyy-mm-dd format

One is dd/mm/yyyy and the other is yyyy-mm-dd.

If this is complete formats list then, for example, use

UPDATE table_name 
SET start_date= STR_TO_DATE(start_date, '%d/%m/%Y')
WHERE LOCATE('/', start_date);


Related Topics



Leave a reply



Submit