How to Use the DATE column in Mysql

The format of the DATE value is 'yyyy-MM-DD '. Other formats are not allowed according to standard SQL statements. This format should be used in the WHERE clause of the UPDATE expression and SELECT statement. For example:

MySQL> SELECT * FROM tbl_name WHERE date> = '2017-2003 ';

For convenience, if the date is used in a numeric environment, MySQL automatically converts the date to a numeric value (and vice versa ). It is also quite intelligent. During an update or in the WHERE clause that compares dates with TIMESTAMP, DATE, or DATETIME columns, it allows a "loose" string (loose) representation, any punctuation character can be used as a separator between parts. For example, '2017-08-15 'is equivalent to '2017 #08 #15 ). MySQL can also convert strings that do not contain any separator (such as '20140901'). The precursor is that it must be a meaningful date.

When you use the <, <=, =, >=,>, or BETWEEN operator to compare DATE, TIME, DATETIME, or TIMESTAMP with a constant string, mySQL usually converts a string to an internal ministerial integer for fast comparison (and a slightly loose string check ). However, this conversion has the following exceptions:

When comparing two columns

When the column DATE, TIME, DATETIME, or TIMESTAMP is compared with the expression

When other comparison methods are used, such as IN or STRCMP ().

For these exceptions, the object is converted to a string and compared with a string.

To ensure security, consider comparing strings by string. If you want to compare temporary values and strings, use the appropriate string function.

The special date '2017-00-00 'can be saved and retrieved in the form of '2017-00-00. When '2017-00-00 'is used in MyODBC, this date is automatically converted to NULL for MyODBC 2.50.12 or later versions, because ODBC cannot process such dates.

Since MySQL can execute the preceding conversions, the following statements can work properly:

Mysql> insert into tbl_name (idate) valuees (19970505 );

Mysql> insert into tbl_name (idate) VALUES ('20140901 ');

Mysql> insert into tbl_name (idate) VALUES ('97-05-05 ');

Mysql> insert into tbl_name (idate) VALUES ('2017. 66661 ');

Mysql> insert into tbl_name (idate) VALUES ('2014, 1997 05 05 ');

Mysql> insert into tbl_name (idate) VALUES ('2017-00-00 ');

Mysql> SELECT idate FROM tbl_name WHERE idate> = '2017-1997 ';

Mysql> SELECT idate FROM tbl_name WHERE idate >=19970505;

Mysql> select mod (idate, 100) FROM tbl_name WHERE idate >=19970505;

Mysql> SELECT idate FROM tbl_name WHERE idate> = '2013 ';

Note: For more exciting tutorials, please pay attention to the tutorial channel for helping customers,