INTERVAL
Example
Section titled “Example”SELECT INTERVAL 7 DAY + '2021-04-01';Output
Section titled “Output”'2021-04-08'Explanation
Section titled “Explanation”The SQL statement above adds seven days to the date ‘2021-04-01’. The INTERVAL keyword is used to specify the period which is to be added, in this case, 7 DAYS.
Example
Section titled “Example”SELECTTIME '02:00:00' + INTERVAL '3 hours';Output
Section titled “Output”05:00:00Explanation
Section titled “Explanation”In this example, INTERVAL '3 hours' adds 3 hours to the given time 02:00:00. The output 05:00:00 is the resultant time after adding the interval.
Example
Section titled “Example”DECLARE @MyTime TIME = '20:00:00';SELECT DATEADD(MINUTE, -20, @MyTime) AS NewTime;Output
Section titled “Output”NewTime-------19:40:00Explanation
Section titled “Explanation”The INTERVAL keyword is not used directly in SQL Server. However, similar functionality where we can increase or decrease a date/time by a certain interval is achieved using the DATEADD function.
In this example, 20 minutes is subtracted from the specified time ‘20:00:00’ using the DATEADD function, which represents an interval of -20 minutes. The result is ‘19:40:00’.
Example
Section titled “Example”SELECT INTERVAL '300' SECOND FROM dual;Output
Section titled “Output”+00 00:05:00.000000000Explanation
Section titled “Explanation”In the above example, the INTERVAL keyword is used to define a time interval of 300 seconds. The SQL query selects this interval from the Oracle dual table, and the output is formatted as an interval of 5 minutes (equivalent to 300 seconds).