2

I have a table that keeps date and hour data as string. I added two fields date2 and hour2 in order to convert and save current string instead of varchar. Date format is dd.mm.YYYY. I want to convert that string to date format and keep it in corresponding column for each row.

Data table structure Data table sample data

I have read "how to convert a string to date in mysql?" tried to query the following statement:

UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%Y-%m-%d')

but I still see NULL values in the date2 column. Nothing is updated.

Community
  • 1
  • 1
zkanoca
  • 9,664
  • 9
  • 50
  • 94
  • 2
    why not use one field as `datetime` to store `date2` and `hour2` into one field? – amaster Sep 11 '13 at 19:18
  • @amaster507, because there are different date formats in the records. Some are mm-dd-yy, some are dd.mm.yyyy, and else mm/dd/yyyy – zkanoca Sep 11 '13 at 20:38

2 Answers2

4

The STR_TO_DATE() function's second parameter specifies the format of the date that you are giving it as a string which in your case does not match the format that is currently in your database. Try using:

UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%d.%m.%Y')
Bad Wolf
  • 8,206
  • 4
  • 34
  • 44
  • Now I see that I have misunderstood the date format. It is current date column's format. But I thought it was desired format. Thank you. that's working fine. – zkanoca Sep 11 '13 at 19:23
0
UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%Y-%m-%d')

should be according to manual:

UPDATE `rawdata` SET `date2` = STR_TO_DATE(`date`, '%d.%m.%Y')

The second parameter, format, corresponds to how your string is currently formatted, not the format of MySQL date.

Alternatively I would combine date2 and hour2 into a datetime field using the following:

UPDATE `rawdata` SET `datetime2` = STR_TO_DATE(CONCAT(`date`,'.',`hour`), '%d.%m.%Y.%H:%i')

That is of course after creating datetime2 field with datetime data type.

amaster
  • 1,915
  • 5
  • 25
  • 51