As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.
Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates.
MySQL Date Functions
The following table lists the most important built-in date functions in MySQL:
| Function | Description |
|---|---|
| NOW() | Returns the current date and time |
| CURDATE() | Returns the current date |
| CURTIME() | Returns the current time |
| DATE() | Extracts the date part of a date or date/time expression |
| EXTRACT() | Returns a single part of a date/time |
| DATE_ADD() | Adds a specified time interval to a date |
| DATE_SUB() | Subtracts a specified time interval from a date |
| DATEDIFF() | Returns the number of days between two dates |
| DATE_FORMAT() | Displays date/time data in different formats |
SQL Server Date Functions
The following table lists the most important built-in date functions in SQL Server:
| Function | Description |
|---|---|
| GETDATE() | Returns the current date and time |
| DATEPART() | Returns a single part of a date/time |
| DATEADD() | Adds or subtracts a specified time interval from a date |
| DATEDIFF() | Returns the time between two dates |
| CONVERT() | Displays date/time data in different formats |
SQL Date Data Types
MySQL comes with the following data types for storing a date or a date/time value 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 comes with the following data types for storing a date or a date/time value 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: a unique number
Note: The date types are chosen for a column when you create a new table in your database!