SQL Date function

SQL date

When processing a date, the most difficult task is to ensure that the format of the inserted Date matches the format of the date column in the database.

As long as the data contains only the date part, running the query will not cause problems. However, the time is complicated.

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 ()	Returns the current date.
CURTIME ()	Returns the current time.
DATE ()	Extract the date part of the date or date/time expression
EXTRACT ()	Returns the separate part of the date/time
DATE_ADD ()	Add a specified interval to a date
DATE_SUB ()	Subtract the specified time interval from the date
DATEDIFF ()	Returns the number of days between two dates.
DATE_FORMAT ()	Display date/time in different formats

SQL Server Date function

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 separate portion of the date/time.
DATEADD ()	Add or subtract a specified time interval from a date
DATEDIFF ()	Returns 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 Processing

If the time section is not involved, we can easily compare two dates!

Suppose 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 record whose OrderDate is "" from the preceding 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

Assume that "Orders" is similar to this (note the time section in the "OrderDate" column ):

OrderId	ProductName	OrderDate
1	Computer	16:23:55
2	Printer	10:45:26
3	Electrograph	14:12:08
4	Telephone	2008-10-19 12:56:10

If we use the preceding SELECT statement: