WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL Date Functions


SQL Dates

Note When we deal with dates, the hardest task is probably to ensure that the format of the inserted date matches the format of the date column in the database .

As long as your data contains only the date part, running the query will not cause problems. However, the situation is a little more complicated when it comes to the time component.

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


MySQL Date function

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

function description
NOW () Returns the current date and time
CURDATE () Return to the current date
CURTIME () Return to the current time
DATE () Extract date part of date or date /time expression
EXTRACT () Returning separate parts of date /time
DATE_ADD () Add specified time interval to date
DATE_SUB () Subtract the specified interval from the date
DATEDIFF () Returns the number of days between two dates
DATE_FORMAT () Date /time display 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 () Returning separate parts of date /time
DATEADD () Add or subtract the specified time interval from the date
DATEDIFF () Returns the time between two dates
CONVERT () Date /time display in different formats

SQL Date data type

MySQL Use the following data types to store dates 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 dates 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

Note: When you create a new table in the database, you need to choose a data type for the column!


SQL date processing

If there is no time part involved, then we can easily compare the two dates!

Suppose we have the following "Orders" table:

OrderId ProductName OrderDate
1 Geitost 2008-11-11
2 Camembert Pierrot 2008-11-09
3 Mozzarella di Giovanni 2008-11-11
4 Mascarpone Fabioli 2008-10-29

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

We use the following SELECT statement:

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

The result set looks like this:

OrderId ProductName OrderDate
1 Geitost 2008-11-11
3 Mozzarella di Giovanni 2008-11-11

Now, suppose the "Orders" table looks like this (note the time part in the "OrderDate" column):

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

If we use the same SELECT statement as above:

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

  or  

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

Then we will not get results! Because there is no "2008-11-11 00:00:00" date in the table. If there is no time part, the default time is 00:00:00.

Tip: If you want to make your queries simpler and easier to maintain, then don't use the time part in the date!