Contents
SQL Date Functions: Main Tips
- Keeping the queries simple to maintain do not use components of time in the dates.
- When you only use the date part in your data the queries will be just fine. As soon as you involve a time part it complicates things.
- Most important thing while using dates is to make sure of the format you are using and the format your database date column is using are the same.
MySQL Date Functions
Most popular functions list of MySQL:
| Function | Description |
|---|---|
| NOW() | Brings back the present date/time |
| CURDATE() | Brings back the present date |
| CURTIME() | Brings back the present time |
| DATE() | Pulls out the date piece of a date/time |
| EXTRACT() | Brings back a single piece of a date/time |
| DATE_ADD() | Inserts a defined time interval to a date |
| DATE_SUB() | Replaces a defined time interval from a date |
| DATEDIFF() | Brings back the amount of days between two dates |
| DATE_FORMAT() | Shows the date/time in various formats |
SQL Server Date Functions
Most popular integrated functions list of SQL Server:
| Function | Description |
|---|---|
| GETDATE() | Brings back the present date and time |
| DATEPART() | Brings back a single piece of a date/time |
| DATEADD() | Inserts or replaces a defined time interval from a date |
| DATEDIFF() | Brings back the time between two dates |
| CONVERT() | Shows the date/time in various formats |
SQL Date: Data Types
MySQL has the data types listed below for containing a date, date/time values:
- DATE - formatting YYYY-MM-DD
- DATETIME - formatting: YYYY-MM-DD HH:MI:SS
- TIMESTAMP - formatting: YYYY-MM-DD HH:MI:SS
- YEAR - formatting YYYY or YY
SQL Server has the data types listed below for containing a date, date/time values:
- DATE - formatting YYYY-MM-DD
- DATETIME - formatting: YYYY-MM-DD HH:MI:SS
- SMALLDATETIME - formatting: YYYY-MM-DD HH:MI:SS
- TIMESTAMP - formatting: a unique digit
SQL Working with Dates
We will be using the table "Order":
| ID | Name | Count | Price | Date |
|---|---|---|---|---|
| 1 | Apple | 120 | 11.9 | 2017-08-18 |
| 2 | Milk | 50 | 9.1 | 2016-12-19 |
| 3 | Bread | 60 | 15.4 | 2017-06-21 |
| 3 | Water | 150 | 12.3 | 2017-07-10 |
| 3 | Chocolate | 85 | 21.9 | 2016-12-25 |
Then we pick out the data records from the column "Date" with "2009-10-10" in the table "Order":
The end result:
| ID | Name | Count | Price | Date |
|---|---|---|---|---|
| 3 | Chocolate | 85 | 21.9 | 2016-12-25 |