SQL Date Function

SQL Date

The most difficult task when dealing with dates is probably to ensure that the format of the date being inserted matches the format of the date column in the database.

If the data contains only the date part, running the query will not cause any problems. However, if time is involved, the situation becomes a bit more complex.

Before discussing the complexity of date queries, let's first look at the most important built-in date processing functions.

MySQL Date Functions

The following table lists the most important built-in date functions in MySQL:

Function Description
NOW() Return the current date and time
CURDATE() Return the current date
CURTIME() Return the current time
DATE() Extract the date part from a date or date/time expression
EXTRACT() Return the individual parts of a date/time
DATE_ADD() Add a specified time interval to a date
DATE_SUB() Subtract a specified time interval from a date
DATEDIFF() Return the number of days between two dates
DATE_FORMAT() Display date/time in different formats

SQL Server Date Functions

The following table lists the most important built-in date functions in SQL Server:

Function Description
GETDATE() Return the current date and time
DATEPART() Return the individual parts of a date/time
DATEADD() Add or subtract a specified time interval from a date
DATEDIFF() Return the time between two dates
CONVERT() Display date/time in different formats

SQL Date data type

MySQL uses the following data types to store date or date/time values 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 uses the following data types to store date or date/time values 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: Unique Number

SQL Date Handling

If it does not involve the time part, we can easily compare two dates!

Assuming we have the following "Orders" table:

OrderId ProductName OrderDate
1 computer 2008-12-26
2 printer 2008-12-26
3 electrograph 2008-11-12
4 telephone 2008-10-19

Now, we want to select the records with OrderDate as "2008-12-26" from the above table.

We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-12-26'

Result Set:

OrderId ProductName OrderDate
1 computer 2008-12-26
3 electrograph 2008-12-26

Now let's assume that the "Orders" class is like this (note the time part in the "OrderDate" column):

OrderId ProductName OrderDate
1 computer 2008-12-26 16:23:55
2 printer 2008-12-26 10:45:26
3 electrograph 2008-11-12 14:12:08
4 telephone 2008-10-19 12:56:10

If we use the above SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-12-26'

Then we cannot get the result. This is because the query does not contain the date part with the time.

Tip:If you want to make the query simple and more maintainable, do not use the time part in the date!