SQL Server and MySQL in the Date function
SQL Date (Dates)
When we deal with the date, it is probably the most difficult task to ensure that the inserted date format match the date format in the database column.
As long as your data contains only the date portion, your queries will go wrong. However, if the time component involved, the situation is a bit complicated.
Before discussing the complexity of the query date, let's look at the most important built-in date processing functions.
MySQL Date Functions
The following table lists the most important built-in MySQL date functions:
| 函数 | 描述 |
|---|---|
| NOW() | 返回当前的日期和时间 |
| CURDATE() | 返回当前的日期 |
| CURTIME() | 返回当前的时间 |
| DATE() | 提取日期或日期/时间表达式的日期部分 |
| EXTRACT() | 返回日期/时间的单独部分 |
| DATE_ADD() | 向日期添加指定的时间间隔 |
| DATE_SUB() | 从日期减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的天数 |
| DATE_FORMAT() | 用不同的格式显示日期/时间 |
SQL Server Date Functions
The following table lists the SQL Server in the most important built-in date functions:
| 函数 | 描述 |
|---|---|
| GETDATE() | 返回当前的日期和时间 |
| DATEPART() | 返回日期/时间的单独部分 |
| DATEADD() | 在日期中添加或减去指定的时间间隔 |
| DATEDIFF() | 返回两个日期之间的时间 |
| CONVERT() | 用不同的格式显示日期/时间 |
SQL Date Data Types
MySQL uses the following types of data stored in the date or date / time values in the database:
- DATE - Format: YYYY-MM-DD
- DATETIME - Format: YYYY-MM-DD HH: MM: SS
- TIMESTAMP - Format: YYYY-MM-DD HH: MM: SS
- YEAR - Format: YYYY or YY
SQL Server uses the following types of data stored in the date or date / time values in the database:
- DATE - Format: YYYY-MM-DD
- DATETIME - Format: YYYY-MM-DD HH: MM: SS
- SMALLDATETIME - Format: YYYY-MM-DD HH: MM: SS
- TIMESTAMP - Format: The only digital
Note: When you create a new table in the database, you need to select a data type for the column!
For all the available data types, please visit our complete Data Types Reference Manual .
SQL Date Processing
If the part does not involve time, then we can easily compare two dates!
Suppose we have the following "Orders" table:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 2 | Camembert Pierrot | 2008-11-09 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
| 4 | Mascarpone Fabioli | 2008-10-29 |
Now we want to select OrderDate as "2008-11-11" records from the table.
We use the following SELECT statement:
The result set as follows:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
Now, suppose that "Orders" table is as follows (please note "OrderDate" column part time):
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 13:23:44 |
| 2 | Camembert Pierrot | 2008-11-09 15:45:21 |
| 3 | Mozzarella di Giovanni | 2008-11-11 11:12:01 |
| 4 | Mascarpone Fabioli | 2008-10-29 14:56:59 |
If we use the same SELECT statement as above:
Then we will get results! This is due to the date of the query contains no time portion.
Tip: If you want to make queries simple and easy to maintain, then please do not use the time components in your dates!