WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL Date Functions


SQL Dates

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.


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:MI:SS
  • TIMESTAMP - format: YYYY-MM-DD HH:MI: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:MI:SS
  • SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP - format: a unique number

SQL Working with Dates

Assume we have the following "Orders" table:

OrderId ProductName OrderDate
1 Julian 2008-11-11
2 Crossett 2008-11-09
3 Crossett j.son 2008-11-11
4 Crossett 2008-10-29

Now we want to select the records with an OrderDate of "2008-11-11" from the table above.

We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

The result-set will look like this:

OrderId ProductName OrderDate
1 McCollum 2008-11-11
3 Austin 2008-11-11

Now, assume that the "Orders" table looks like this (notice the time component in the "OrderDate" column):

OrderId ProductName OrderDate
1 Geitost 2018-01-12 13:23:44
2 Camembert Pierrot 2018-12-13 15:45:21
3 Mozzarella di 2008-11-14 11:12:01
4 Mascarpone Fabioli 2018-11-22 14:56:59

If we use the same SELECT statement as above:

SELECT * FROM Orders WHERE OrderDate='2018-11-22'