CODEWITHSUNDEEP
mysqlsql
user11621936
user11621936

Reputation:

INSERT date into table

In MySQL I want to insert a new row into a table using the date function.

Should I code the statement like this?

But how should I format the date?

Upvotes: 0

Views: 120

Answers (3)

spencer7593
spencer7593

Reputation: 108370

We can use MySQL STR_TO_DATE function with a format model that matches the string we want to convert to DATE.

As a demonstration: 

SELECT STR_TO_DATE('April 1, 2014','%M %e, %Y')

This converts the string into a DATE value. Default representation for DATE values is YYYY-MM-DD, so if we run that query, we expect it to return 2014-04-01.

We can use an expression as a value in an INSERT statement. For example, assuming the target column is datatype DATE (or DATETIME or TIMESTAMP) and is named mydatecol, we could do something like this:

INSERT INTO mytable (foo,mydatecol) VALUES ('bar',STR_TO_DATE('April 1, 2014','%M %e, %Y'))

MySQL functions are documented in MySQL Reference Manual

STR_TO_DATE

Reference: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

The format model/specifiers are documented under DATE_FORMAT function

Reference: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Find %M, %e and %Y in in the table of specifiers for meanings.

Upvotes: 0

robertus
robertus

Reputation: 356

From ANSI SQL-92 standard (ISO/IEC 9075:1992, Database Language SQL- July 30, 1992):

<date literal> ::=
              DATE <date string>

<date string> ::=
              <quote> <date value> <quote>

<date value> ::=
              <years value> <minus sign> <months value> <minus sign> <days value>

<years value> ::= <datetime value>
<months value> ::= <datetime value>
<days value> ::= <datetime value>

<datetime value> ::= <unsigned integer>

Let unsigned integer be an axiom, then that text above translates to example date literal below:

DATE '2014-04-01'

It should work in almost all relational databases. All modern RDBMS have their own extensions, so I'm recommending you to read MySQL Reference Manual (chapter: Date and Time Literals) to learn more.

Upvotes: 1

Acy.Forsythe
Acy.Forsythe

Reputation: 58

Select DATE_FORMAT("2014-04-01","%M %d, %Y");

A quick Google Search on MySQL Date format will bring you documentation on this specific function. It probably took you longer to ask the question here than it would have to find the answer on say... W3Schools or even dev.mysql.com.

Upvotes: 0

Related Questions