Easy Tutorial
Sql Having Sql Func Mid Sql Between Sql Drop Sql Wildcards Sql Join Inner Sql Join Full Sql Func Lcase Sql Where Sql Join Sql Func Round Sql Primarykey Sql Func First Sql Func Len Sql Distinct Sql Func Ucase Sql Function Sql Delete Sql Autoincrement Func Curtime Sql Update Sql Isnull Func Date Sub Sql Constraints Sql Intro Sql Dates Sql Select Into Sql Hosting Sql Create Table Sql Func Format Sql View Sql Func Count Sql Index Sql Like Sql Datatypes General Sql Datatypes Func Convert Sql Func Avg Sql Foreignkey Sql Func Sum Sql Join Right Func Curdate Sql Alias Sql Union Func Date Add Sql Groupby Sql Summary Sql In Sql Null Values Sql Syntax Sql Tutorial Sql Insert Into Select Sql And Or Sql Join Left Sql Func Now Func Date Sql Top Index Sql Create Db Sql Unique Sql Check Func Extract Sql Alter Sql Notnull Func Now Sql Select Sql Insert Sql Func Max Sql Exists Sql Func Min Func Date Format Func Datediff Mysql Func Getdate Sql Orderby Func Datediff Sql Func Last Sql Create Index Func Dateadd Sql Quickref Func Datepart
❮ Sql Intro Sql Select Into ❯

SQL Date Function

SQL Dates

As long as your data contains only the date portion, your queries will work fine. However, if you involve the time part, things get a bit more complicated.

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

MySQL Date Functions

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() Extracts the date part of a date or date/time expression
EXTRACT() Returns a single part of a date/time
DATE_ADD() Adds a specified time interval to a date
DATE_SUB() Subtracts a specified time interval from a date
DATEDIFF() Returns the number of days between two dates
DATE_FORMAT() Displays date/time data 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() Returns a single part of a date/time
DATEADD() Adds or subtracts a specified time interval from a date
DATEDIFF() Returns the time between two dates
CONVERT() Displays date/time data in different formats

SQL Date Data Types

MySQL uses the following data types to store date or date/time values in the database:

SQL Server uses the following data types to store date or date/time values in the database:

Note: When you create a new table in your database, you need to select a data type for the columns!

For a complete reference of all the available data types, visit our full Data Types Reference Manual.

SQL Date Handling

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 records from the above table with an OrderDate of "2008-11-11".

We use the following SELECT statement:

The result set will look 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'

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

Tip: If you want to make your query simpler and easier to maintain, do not use the time part in the date!

❮ Sql Intro Sql Select Into ❯