CODEWITHSUNDEEP
mysqlsqldatetimesql-updatedate-format
JasonD
JasonD

Reputation: 23

Can i convert a mysql field with invalid microseconds value to a datetime format?

I have a table imported from a csv file. However, the date field isn't not formatted nicely.

Is it possible to convert this string using a mysql STR_TO_DATE function?

I need this '05/11/2009 16:07:53:052' to be converted as a datetime format such like '2009-05-11 16:07:53' and ignoring the microsecs..

I tried using something like this

UPDATE mytable
SET updated_on = DATE(STR_TO_DATE(updated_on, '%Y-%m-%d %H:%i:%s'))

And

UPDATE mytable
SET updated_on = DATE(STR_TO_DATE(updated_on, GET_FORMAT(DATETIME,'ISO')))

But no luck, please help!

Thanks

Upvotes: 2

Views: 1666

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Check STR_TO_DATE function

Try this:

If the date format is mm/dd/yyyy hh:mm:ss:sss then 

UPDATE mytable SET updated_on = STR_TO_DATE(updated_on, '%m/%d/%Y %H:%i:%s');

If the date format is dd/mm/yyyy hh:mm:ss:sss then 

UPDATE mytable SET updated_on = STR_TO_DATE(updated_on, '%d/%m/%Y %H:%i:%s');

Upvotes: 0

Ravinder Reddy
Ravinder Reddy

Reputation: 24022

You need proper symbol to represent microsecond. It is %f. 

mysql> select str_to_date( '05/11/2009 16:07:53:052', '%d/%m/%Y %H:%i:%s:%f' );
+------------------------------------------------------------------+
| str_to_date( '05/11/2009 16:07:53:052', '%d/%m/%Y %H:%i:%s:%f' ) |
+------------------------------------------------------------------+
| 2009-11-05 16:07:53.052000                                       |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

You can omit the time format part just to return date part, but with a warning on data truncation. 

mysql> select str_to_date( '05/11/2009 16:07:53:052', '%d/%m/%Y' );
+------------------------------------------------------+
| str_to_date( '05/11/2009 16:07:53:052', '%d/%m/%Y' ) |
+------------------------------------------------------+
| 2009-11-05                                           |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '05/11/2009 16:07:53:052' |
+---------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

Refer to:

  1. MySQL: Date and Time Functions
  2. on the same page, a useful reference table on format specifier symbols

Upvotes: 2

Nagaraj S
Nagaraj S

Reputation: 13484

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

The server scans str attempting to match format to it. The format string can contain literal characters and format specifiers beginning with %. Literal characters in format must match literally in str. Format specifiers in format must match a date or time part in str

UPDATE mytable SET updated_on = STR_TO_DATE(updated_on, '%m/%d/%Y %H:%i:%s')

Upvotes: 0

Related Questions