0

How can I convert the following date format into a datetime format in MySQL? Right now it is stored as a varchar. Should I use a Python script, or is there an inbuilt MySQL function which will complete it?

    27 07 (2009), 10:38 PM,
Sagar Samtani
  • 203
  • 1
  • 4
  • 10
  • Have you tried using `CAST` function like `CAST(datecolumn as DATETIME)` – Rahul Jun 19 '15 at 22:14
  • [how to convert varchar to datetime format in mysql](http://stackoverflow.com/questions/15396058/how-to-convert-varchar-to-datetime-format-in-mysql)? [MySQL convert Varchar to DateTime](http://stackoverflow.com/questions/22518722/mysql-convert-varchar-to-datetime)? [mySQL convert varchar to date](http://stackoverflow.com/questions/22518722/mysql-convert-varchar-to-datetime)? If there's something that those questions don't answer, ask for it more specifically. – Jashaszun Jun 19 '15 at 22:14

3 Answers3

2

like this

SELECT STR_TO_DATE('27 07 (2009), 10:38 PM', '%d %m (%Y), %l:%i %p');

Here are the specifiers: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

If the original text contains a trailing comma, you just match it in the pattern

SELECT STR_TO_DATE('27 07 (2009), 10:38 PM,', '%d %m (%Y), %l:%i %p,');

If you want to update the column, you can run an update statement like:

update mytable 
set dateat = STR_TO_DATE(dateat, '%d %m (%Y), %l:%i %p,')

And don't forget to alter your field from varchar to datetime

Fabricator
  • 12,722
  • 2
  • 27
  • 40
  • Is there someway to first remove the comma and to also update the whole column? – Sagar Samtani Jun 19 '15 at 22:22
  • Running the command through MySQL Workbench yielded this response: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect. 0.032 sec – Sagar Samtani Jun 19 '15 at 22:36
  • @SagarSamtani, you need to disable `safe update` like http://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench – Fabricator Jun 19 '15 at 22:39
1

You can use str_to_date function in mysql.

str_to_date(column,format)

Example: select str_to_date("27 07 (2009), 10:38 pm","%d %m (%Y), %l:%i %p") from table

Francisco Escobar
  • 241
  • 2
  • 8
1

You can use this statement

SELECT STR_TO_DATE('27 07 (2009), 10:38 PM, '%d %m(%Y),%H:%i');

This will give you 2009-07-27 22:38:00

No need to worry about PM or AM. %H will take care of that and mention 22 for 10PM and the PM will be redundant then.

aqqq
  • 83
  • 8