Converting String Data Value into date

Question

Good Morning All;

I currently have a MySQL table where there are 3 date fields (Columns) that were loaded as strings in this format 20140101 YYYYmmdd. I would like to convert this to a date format 2014/01/01 YYYY/mm/dd. Can someone please provide a simple sql syntax that would alter the table to a date format from a string and change the column to display the dates like this 2014/01/01 and not like 20140101. Thanks to all

MySQL provides a datatype DATE specifically designed to store and manipulate "date" values. There's no "format" for DATE columns per se, but MySQL uses a default format YYYY-mm-dd for date string literals, and when converting DATE to/from VARCHAR. MySQL provides functions FORMAT_DATE and STR_TO_DATE to convert between DATE and VARCHAR, and you can use string values in lots of formats, just provide appropriate format specifier e.g. '%Y/%m/%d'. Did you want to keep the column VARCHAR and just change the contents of the column, or did you want to convert the column to DATE? — spencer7593
– spencer7593, Commented Jun 1, 2015 at 16:20

Rahul Tripathi · Accepted Answer · 2015-06-01 15:54:56Z

1

Try this:

date_format(str_to_date(datecolumn, '%Y%m%d'),'%Y/%m/%d')

answered Jun 1, 2015 at 15:54

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

spencer7593 · Accepted Answer · 2015-06-01 16:13:07Z

If you just want to reformat the values in the VARCHAR column, assuming that the column with sufficient length e.g. VARCHAR(10), and all the values are eight characters in length...

You could do something like this:

UPDATE mytable t
   SET t.mycol = CONCAT( LEFT( t.mycol ,4)
                       , '/'
                       , SUBSTR( t.mycol ,5,2)
                       ,'/'
                       , SUBSTR( t.mycol ,7,2)
                      ) 
 WHERE CHAR_LENGTH(t.mycol) = 8

We want something in the statement that will prevent the statement from "working" a second time, if it's inadvertently re-run. It doesn't have to be CHAR_LENGTH. We might want to include a check that the value doesn't already contain a slash character AND t.mycol NOT LIKE '%/%'.

But why on earth are "date" values being stored in character columns, rather than in DATE datatype, which is custom designed for storing and working with date values?

ALTER TABLE mytable MODIFY mycol DATE ... ;

(If the column is defined as NOT NULL, has a default value, has a comment, those attributes can be retained, they need to be included in the new column specification, e.g.

ALTER TABLE mytable MODIFY mycol DATE NOT NULL COMMENT 'creation date';

Note that DATE columns do not have a "format" per se. When converting to string, MySQL uses date format '%Y-%m-%d'. And MySQL expects string literals representing date values to be in that same format. To get a value from a DATE column converted to string in format 'yyyy/mm/dd'.

  SELECT DATE_FORMAT(date_col,'%Y/%m/%d') AS date_col

To get a string value in that format converted to DATE datatype

 SELECT STR_TO_DATE('2015/06/01','%Y/%m/%d')

Collectives™ on Stack Overflow

Converting String Data Value into date

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related