I have the string 04/04/2012 04:03:35 AM
. How can I convert it and insert into a table? Field type is datetime
.
Always use ANSI default string literal format for date i.e. YYYY-MM-DD like below. INSERT INTO EMPLOYEE (EMPID, FULLNAME, DESIGNATION, JOINING, SAL, DEPTNAME) VALUES(8976, 'JOHN', 'JOE', 'ANALYST', '1990-12-12', 30000, 'Analytics'); It will insert your data in RDBMS i.e. MySQL, PostgreSQL, SQL Server.
How do I add a TIMESTAMP to a MySQL table? Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.
You can use now() with default auto fill and current date and time for this. Later, you can extract the date part using date() function. Let us set the default value with some date.
You need to use STR_TO_DATE()
:
STR_TO_DATE('04/04/2012 04:03:35 AM', '%d/%m/%Y %r')
Or, better yet, present your literal in a format MySQL expects (e.g. YYYY-MM-DD HH:mm
).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With