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.
No comments:
Post a Comment