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 isyyyy-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
How to Make SQL Query Result Show With 2 Decimals
Create a Query That Will Display Employee Name and Department Number
How to Find Specific Values in a Table in Oracle
Mysql - Trigger for Updating Same Table After Insert
Insert Multiple Rows Without Repeating the "Insert into ..." Part of the Statement
Multiple Rows Output into Variables in MySQL
Mysql - Move Rows from One Table to Another
Grouping But With Keeping All Non-Null Values
Concatenate With String But Exclude When Null
Spring Data JPA Utf-8 Encoding Not Working
How to Select All the Columns of a Table Except One Column
Query: Fetch 3 Records Which Has Higher Value
Unioning Two Tables With Different Number of Columns
Select All Dates Between First Day of Month and Current Date
Select Rows Where Every Child Row Meets a Condition
Multiplying Two Columns in SQL Server