SQL DATES


As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets complicated.

Before talking about the complications of querying for dates, we will look at the most important built-in functions for working with dates.


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

Top

NOW() Function

NOW() returns the current date and time.

Syntax

NOW()

Example

SELECT NOW()

Top

CURDATE() Function

CURDATE() returns the current date.

Syntax

CURDATE()

Example

SELECT CURDATE()

Top

CURTIME() Function

CURTIME() returns the current time.

Syntax

CURTIME()

Example

SELECT CURTIME()

Top

DATE() Function

The DATE() function extracts the date part of a date or date/time expression.

Syntax

DATE(date)

Top

EXTRACT() Function

The EXTRACT() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

Syntax

EXTRACT(unit FROM date)

Where date is a valid date expression and unit can be one of the following:

Unit Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

Top

DATE_ADD() Function

The DATE_ADD() function adds a specified time interval to a date.

Syntax

DATE_ADD(date,INTERVAL expr type)

Where date is a valid date expression and expr is the number of interval you want to add.

Type Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

Example

SELECT orderId, DATE_ADD(orderDate,INTERVAL 45 DAY) AS orderPayDate
FROM orders

Top

DATE_SUB() Function

The DATE_SUB() function subtracts a specified time interval from a date.

Syntax

DATE_SUB(date,INTERVAL expr type)

Where date is a valid date expression and expr is the number of interval you want to add.

Type Value
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

Example

SELECT orderId,DATE_SUB(orderDate,INTERVAL 5 DAY) AS subtractDate
FROM orders

Top

DATEDIFF() Function

DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF(date1,date2)

Where date1 and date2 are valid date or date/time expressions.

Example

SELECT DATEDIFF('2015-1-20','2015-11-10') AS DiffDate

Top

DATE_FORMAT() Function

The DATE_FORMAT() function is used to display date/time data in different formats.

Syntax

DATE_FORMAT(date,format)
FormatDescription
%aAbbreviated weekday name
%bAbbreviated month name
%cMonth, numeric
%DDay of month with English suffix
%dDay of month, numeric (00-31)
%eDay of month, numeric (0-31)
%fMicroseconds
%HHour (00-23)
%hHour (01-12)
%IHour (01-12)
%iMinutes, numeric (00-59)
%jDay of year (001-366)
%kHour (0-23)
%lHour (1-12)
%MMonth name
%mMonth, numeric (00-12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00-53) where Sunday is the first day of week
%uWeek (00-53) where Monday is the first day of week
%VWeek (01-53) where Sunday is the first day of week, used with %X
%vWeek (01-53) where Monday is the first day of week, used with %x
%WWeekday name
%wDay of the week (0=Sunday, 6=Saturday)
%XYear of the week where Sunday is the first day of week, four digits, used with %V
%xYear of the week where Monday is the first day of week, four digits, used with %v
%YYear, four digits
%yYear, two digits

Example

DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

Top

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

Top

GETDATE() Function

GETDATE() function returns the current date and time from the SQL Server.

Syntax

GETDATE()

Example

SELECT GETDATE() AS CurrentDateTime

Top

DATEPART() Function

DATEPART() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc.

Syntax

DATEPART(datepart,date)

Where date is a valid date expression and datepart can be one of the following:

datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

Example

SELECT GETDATE() AS CurrentDateTime

Top

DATEADD() Function

DATEADD() function adds or subtracts a specified time interval from a date.

Syntax

DATEADD(datepart,number,date)

Where date is a valid date expression and number is the number of interval you want to add. The number can either be positive, for dates in the future, or negative, for dates in the past.

datepart can be one of the following:

datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

Example

SELECT orderId,DATEADD(day,45,orderdate) AS orderPayDate
FROM orders

Top

DATEDIFF() Function

DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF(datepart,startdate,enddate)

Where startdate and enddate are valid date expressions and datepart can be one of the following:

datepartAbbreviation
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns

Example

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS diffDate

Top

CONVERT() Function

The CONVERT() function is a general function that converts an expression of one data type to another.

The CONVERT() function can be used to display date/time data in different formats.

Syntax

CONVERT(data_type(length),expression,style)
ValueDescription
data_type(length)Specifies the target data type (with an optional length)
expressionSpecifies the value to be converted
styleSpecifies the output format for the date/time

The table below represent the style values for datetime or smalldatetime conversion to character data:

Value
(century yy)
Value
(century yyyy)
Input/OutputStandard
-0 or 100mon dd yyyy hh:miAM (or PM)Default
1101mm/dd/yyUSA
2102yy.mm.ddANSI
3103dd/mm/yyBritish/French
4104dd.mm.yyGerman
5105dd-mm-yyItalian
6106dd mon yy 
7107Mon dd, yy 
8108hh:mm:ss 
-9 or 109mon dd yyyy hh:mi:ss:mmmAM (or PM)Default+millisec
10110mm-dd-yyUSA
11111yy/mm/ddJapan
12112yymmddISO
-13 or 113dd mon yyyy hh:mi:ss:mmm (24h) 
14114hh:mi:ss:mmm (24h) 
-20 or 120yyyy-mm-dd hh:mi:ss (24h) 
-21 or 121yyyy-mm-dd hh:mi:ss.mmm (24h) 
-126yyyy-mm-ddThh:mi:ss.mmm (no spaces)ISO8601
-130dd mon yyyy hh:mi:ss:mmmAMHijiri
-131dd/mm/yy hh:mi:ss:mmmAMHijiri

Example

CONVERT(VARCHAR(19),GETDATE())
CONVERT(VARCHAR(10),GETDATE(),10)
CONVERT(VARCHAR(10),GETDATE(),110)
CONVERT(VARCHAR(11),GETDATE(),6)
CONVERT(VARCHAR(11),GETDATE(),106)
CONVERT(VARCHAR(24),GETDATE(),113)

Top

SQL Date Data Types

MySQL comes with the following data types for storing a date or a date/time value 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 comes with the following data types for storing a date or a date/time value 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: a unique number

Share this article on