SQL Date
When we work on dates, the hardest task is to ensure that the date you insert is formatted to match the format of the date column in the database.
As long as the data contains only the date parts, running the query will not be a problem. However, if time is involved, the situation is a little more complicated.
Before we discuss the complexity of the date query, let's 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 date part of date or date/time expression |
EXTRACT () |
Returns a separate part of the date/time Press |
Date_add () |
Add a specified time interval to a date |
Date_sub () |
Subtract a specified time interval from a date |
DATEDIFF () |
Returns the number of days between two dates |
Date_format () |
Display Date/time in a different format |
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 part of the date/time |
DATEADD () |
Add or subtract a specified time interval in a date |
DATEDIFF () |
Returns a time between two dates |
CONVERT () |
Display Date/time in a different format |
SQL Date Data type
MySQL stores date or date/time values in the database using the following data types:
- 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 part is not involved, then 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 a record from the table above that OrderDate is "2008-12-26".
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 suppose that "Orders" is similar (note the time section 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 SELECT statement above:
SELECT * from Orders WHERE orderdate= ' 2008-12-26 '
Then we won't get the result. This is because the query does not contain a date for the time part.
Tip: If you want to make queries simple and easier to maintain, don't use the time section in dates!
SQL Advanced App (date function)