SQL Date function
Note: When we work with dates, the most difficult task may be to make sure that the format of the insert date matches the format in the date column in the database.
Running a query is not a problem as long as your data contains only a portion of the date. However, when it comes to time, the situation is a little more complicated.
Before discussing the complexity of date queries, let's look at the most important built-in date handlers.
MySQL Date function
The following table lists the most important built-in date functions in MySQL:
| function | describe |
|---|---|
| NOW() | Returns the current date and time |
| CURDATE() | Returns the current date |
| CURTIME() | Returns the current time |
| DATE() | Extract date or date / time expression date part |
| EXTRACT() | Returns a separate section of the date / time |
| DATE_ADD() | Add a specified time interval to the date |
| DATE_SUB() | Split the specified time lapse from the date |
| DATEDIFF() | Returns the number of days between two dates |
| DATE_FORMAT() | Display date / time with different formats |
SQL Server Date function
The following table lists the most important built-in date functions in SQL servers:
| function | describe |
|---|---|
| GETDATE() | Returns the current date and time |
| DATEPART() | Returns a separate section of the date / time |
| DATEADD() | Add or subtract the specified time interval in the date |
| DATEDIFF() | Returns the time between two dates |
| CONVERT() | Display date / time with different formats |
SQL Date data type
MySQL stores date or time values in the database using the following data types:
- 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 stores date or time values in the database using the following data types:
- DATE - Format: YYYY-MM-DD
- DATETIME - Format: YYYY-MM-DD HH:MM:SS
- SMALLDATETIME - Format: YYYY-MM-DD HH:MM:SS
- TIMESTAMP - Format: Unique number
Note: When you create a new table in the database, you need to select a data type for the column!
For all available data types, visit our complete Data Type Reference Manual.
SQL date processing
Note: If
you don't cover the time section, 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" from the table above.
Let's use the following SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2008-11-11' The result set looks like this:
| OrderId | ProductName | OrderDate |
|---|---|---|
| 1 | Geitost | 2008-11-11 |
| 3 | Mozzarella di Giovanni | 2008-11-11 |
Now, suppose the Orders table looks like this (note the time portion in the OrderDate column):
| 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:
SELECT * FROM Orders WHERE OrderDate='2008-11-11' So we won't get results! This is because the date of the query does not contain a time section.
Tip: If you want to make queries simpler and easier to maintain, don't use the time portion of the date!