SQL Date Functions

SQL > SQL Date Functions

In this section, we cover common date functions seen in SQL. Different database systems have different formats for date-type data, and each RDBMS may employ different date functions, and there may also be differences in the syntax for each RDBMS even when the function call is the same. All such differences are explained in each section. Please note that we do not list all possible SQL date functions in this tutorial. Rather, the most commonly used ones are covered.

• SQL DATEADD Function
• SQL DATEDIFF Function
• SQL DATEPART Function
• SQL GETDATE Function
• SQL SYSDATE Function

SQL > SQL Date Functions > Dateadd Function

The DATEADD function is used to add an interval to a date. This function is available in SQL Server.

The usage for the DATEADD function is

DATEADD (datepart, number, expression)

where the data type of <expression> is some type of date, time, or datetime. <number> is an integer (can be positive or negative). <datepart> can be one of the following:

datepart	abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

The result returned has the same data type as <expression>.

Example: The SQL statement

SELECT DATEADD (day, 10, '2000-01-05 00:05:00.000');

yields the following result:

'2000-01-15 00:05:00.000'

SQL > SQL Date Functions > Datediff Function

The DATEDIFF function is used to calculate the difference between two days, and is used in MySQL and SQL Server. The syntax for this date function is different between these two databases, so each one is discussed below:

MySQL:

The usage for the DATEDIFF function in MySQL is

DATEDIFF (expression1, expression2)

where the data type of <expression1> and <expression2> is either DATE or DATETIME. The result is <expression1> - <expression2>.

Example: The SQL statement

SELECT DATEDIFF ('2000-01-10', '2000-01-05');

yields the following result:

5

This is because 2000-01-10 is 5 days after 2000-01-05.

SQL Server:

The usage for the DATEDIFF function in SQL Server is

DATEDIFF (datepart, expression1, expression2)

where the data type of <expression1> and <expression2> is some type of date, time, or datetime. The result is <expression2> - <expression1>. datepart can be one of the following:

datepart	abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

Example: The SQL statement

SELECT DATEDIFF (day, '2000-01-10','2000-01-05');

yields the following result:

-5

This is because 2000-01-05 is 5 days before 2000-01-10.

SQL > SQL Date Functions > Datepart Function

DATEPART is a SQL Server function that extracts a specific part of the date/time value. Its syntax is as follows:

DATEPART (part_of_day, expression)

where part_of_day can have the following:

datepart	abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

Example 1

SELECT DATEPART (yyyy, '2000-01-20');

Result:

2000

Example 2

SELECT DATEPART (dy, '2000-02-10');

Result:

41

2000-02-10 is the 41st day in the year 2000.

SQL > SQL Date Functions > Getdate FunctionThe GETDATE function is used to retrieve the current database system time in SQL Server. Its syntax is GETDATE( ) GETDATE does not require any argument. Example: The SQL statement SELECT GETDATE( ); yields the following result: '2000-03-15 00:05:02.123' GETDATE function is most useful when we need to record the time a particular transaction happens. In SQL Server, we simply insert the value of the GETDATE( ) function into the table to achieve this. We can also set the default value of a column to be GETDATE( ) to achieve the same purpose. The Oracle and MySQL equivalent of GETDATE is SYSDATE.

SQL > SQL Date Functions > Sysdate Function

The SYSDATE function is used to retrieve the current database system time in Oracle and MySQL.

Oracle:

The syntax of SYSDATE in Oracle is simply

SYSDATE

It does not require any argument.

Example

SELECT SYSDATE FROM DUAL;

Result:

SYSDATE

16-JAN-2000

MySQL:

The syntax of SYSDATE in MySQL is simply

SYSDATE( )

It does not require any argument.

Example

SELECT SYSDATE( );

Result:

SYSDATE

2000-01-16 09:06:22

The SQL Server equivalent of SYSDATE is GETDATE.