SQL date

---

SQL Dates

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

As long as your data contains only the date portion, there will be no problem running the query. However, when it comes to the time part, things get a little more complicated.

Before discussing the complexity of date queries, let's first 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 the current date
CURTIME()	Return the current time
DATE()	Extract the date part of a date or date/time expression
EXTRACT()	Return the individual part of the date/time
DATE_ADD()	Adds the specified time interval to the date
DATE_SUB()	Subtracts the specified time from the date Interval
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 common functions in SQL Server Important built-in date functions:

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

---

SQL Date Data Type

MySQL Use 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 Use 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

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

To learn about all available data types, visit our complete Data Types Reference Manual.

---

SQL date processing

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

Suppose we have the following "Orders" table:

##2Camembert Pierrot2008-11-093Mozzarella di Giovanni2008-11-114Mascarpone Fabioli2008-10-29##Now, we want to select the OrderDate from the above table as "2008-11-11" record.

OrderId	ProductName	OrderDate
1	Geitost	2008-11-11

We use the following SELECT statement:

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

OrderId##1 Geitost2008-11-113Mozzarella di Giovanni2008-11-11Now, suppose the "Orders" table looks like this (note the time part in the "OrderDate" column):

ProductName	OrderDate

OrderIdProductNameOrderDate1Geitost2008-11-11 13:23:442Camembert Pierrot2008-11-09 15:45:213Mozzarella di Giovanni2008-11-11 11:12:014Mascarpone Fabioli2008-10-29 14:56:59

If we use the same SELECT statement as above:

then we will have To the result! This is because the query's date does not contain a time component.

Tip: If you want to keep your query simple and easier to maintain, don't use the time part in the date!