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!