Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000

What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000

Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the period: ante or post meridiem

In the DATE_FORMAT part we se a %k which is in 24hr format in order to loose the period.

A more detailed list is found here

Here is a demo:

mysql Tue Jun 15 12:32:37 2010 > CREATE TABLE test.abc(a DECIMAL(17,3)) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)

mysql Tue Jun 15 12:32:45 2010 > INSERT INTO abc VALUES ( DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f') );
Query OK, 1 row affected (0.01 sec)

mysql Tue Jun 15 12:32:51 2010 > SELECT * FROM abc;
+--------------------+
| a                  |
+--------------------+
| 20080316211251.893 |
+--------------------+
1 row in set (0.00 sec)

mysql Tue Jun 15 12:32:56 2010 > SELECT TIMESTAMP(a) FROM abc;
+----------------------------+
| TIMESTAMP(a)               |
+----------------------------+
| 2008-03-16 21:12:51.893000 |
+----------------------------+
1 row in set (0.00 sec)

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: +1 (from 1 vote)

  1. shon says:

    I get the following exception when trying to run “select timestamp(a) from abc;”

    System.FormatException
    Input string was not in a correct format.
    Stack Trace:
    at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
    at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
    at MySql.Data.Types.MySqlDateTime.ParseMySql(String s)
    at MySql.Data.Types.MySqlDateTime.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
    at MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
    at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
    at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
    at MySql.Data.MySqlClient.MySqlDataReader.Read()
    at Quest.Toad.Db.ToadDataAdapter.InternalReadBackground()

  2. Hi Shon,
    If you follow the instructions above it’ll work fine. If you are setting a to some other data type it might not work.

    mysql Tue Sep 6 19:39:19 2011 > use test;
    Database changed

    mysql Tue Sep 6 19:39:31 2011 > CREATE TABLE test.abc(a DECIMAL(17,3)) ENGINE=MYISAM;
    Query OK, 0 rows affected (0.01 sec)

    mysql Tue Sep 6 19:39:31 2011 > INSERT INTO abc VALUES ( DATE_FORMAT(STR_TO_DATE(‘MAR 16 2008 09:12:51:893PM’,’%M %d %Y %h:%i:%s:%f%p’),’%Y%m%d%k%i%s.%f’) );
    Query OK, 1 row affected (0.01 sec)

    mysql Tue Sep 6 19:39:48 2011 > SELECT * FROM abc;
    +——————–+
    | a |
    +——————–+
    | 20080316211251.893 |
    +——————–+
    1 row in set (0.00 sec)

    mysql Tue Sep 6 19:39:53 2011 > SELECT TIMESTAMP(a) FROM abc;
    +—————————-+
    | TIMESTAMP(a) |
    +—————————-+
    | 2008-03-16 21:12:51.893000 |
    +—————————-+
    1 row in set (0.01 sec)

    mysql Tue Sep 6 19:56:52 2011 > show variables like ‘sql_mode’;
    +—————+——————————————————————————————————————————————————+
    | Variable_name | Value |
    +—————+——————————————————————————————————————————————————+
    | sql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +—————+——————————————————————————————————————————————————+
    1 row in set (0.00 sec)

*