Code has been added to clipboard!

SQL DATE Syntax and Examples

Reading time 2 min
Published Aug 9, 2017
Updated Oct 11, 2019

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
Theory is great, but we recommend digging deeper!

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":

Example
SELECT * FROM Orders WHERE Date='2016-12-25'

The end result:

ID Name Count Price Date
3 Chocolate 85 21.9 2016-12-25