MySQL date formats - difficulty Inserting a date

Question

I am trying to further a question I asked yesterday where I wanted to know how to query a date in a different format  But now I am trying to do an insert using this method  see below  however I can t get it to work  I have checked the manual but it is not beginner friendly   INSERT INTO custorder VALUES   Kevin   yes    STR TO DATE  1-01-2012     d- m- Y

User · Accepted Answer

Put the date in single quotes and move the parenthesis (after the 'yes') to the end:

INSERT INTO custorder 
  VALUES ('Kevin', 'yes' , STR_TO_DATE('1-01-2012', '%d-%m-%Y') ) ;
                        ^                                     ^
---parenthesis removed--|                and added here ------|

But you can always use dates without STR_TO_DATE() function, just use the (Y-m-d) '20120101' or '2012-01-01' format. Check the MySQL docs: Date and Time Literals

INSERT INTO custorder 
  VALUES ('Kevin', 'yes', '2012-01-01') ;

User · Answer

An add-on to the previous answers since I came across this concern   If you really want to insert something like 24-May-2005 to your DATE column  you could do something like this   INSERT INTO someTable Empid Date Joined  VALUES       S710  STR TO DATE  24-May-2005     d- M- Y       In the above query please note that if it s May ie  the month in letters  the format should be  M   NOTE  I tried this with the latest MySQL version 8 0 and it works

User · Answer

The date format for mysql insert query is YYYY-MM-DD  example   INSERT INTO table name  date column  VALUE   YYYY-MM-DD

User · Answer

Looks like you ve not encapsulated your string properly  Try this   INSERT INTO custorder VALUES   Kevin   yes    STR TO DATE  1-01-2012     d- m- Y      Alternatively  you can do the following but it is not recommended  Make sure that you use STR TO-DATE it is because when you are developing web applications you have to explicitly convert String to Date which is annoying  Use first One   INSERT INTO custorder VALUES   Kevin   yes     2012-01-01    I m not confident that the above SQL is valid  however  and you may want to move the date part into the brackets  If you can provide the exact error you re getting  I might be able to more directly help with the issue

User · Answer

When using a string-typed variable in PHP containing a date  the variable must be enclosed in single quotes    NEW DATE    1997-07-15    sql    INSERT INTO tbl  NEW DATE       VALUES    NEW DATE

[mysql] MySQL date formats - difficulty Inserting a date

Examples related to mysql

Examples related to date

Examples related to insert

Examples related to format