You can use ODBC scalar functions when running T-SQL statements in SQL Server. There are various types of ODBC scalar functions, including string functions, numeric functions, system functions, and of course, date/time functions (also referred to as date, time, and interval functions).
Below are examples of the date, time and interval functions you can use in SQL Server.
Syntax
First, here’s the syntax you need to use whenever you call an ODBC scalar function:
SELECT {fn <function_name> [ (<argument>,....n) ] }
Where <function_name> is the name of the function and (<argument>,....n) is any number of optional arguments.
So the main thing to remember is that when you call an ODBC scalar function, you need to enclose the function call within curly braces ({}) and prefix the name with fn.
Example 1 – NOW()
This function returns current date and time as a timestamp value.
SELECT {fn NOW()} AS Result;
Result:
+-------------------------+ | Result | |-------------------------| | 2018-06-13 21:52:25.667 | +-------------------------+
Example 2 – Timestamp, Date, and Time
The CURRENT_TIMESTAMP() function returns the current local date and local time as a timestamp value. You can also use the CURRENT_DATE() to return only the date portion, and/or CURRENT_TIME() to return only the time portion.
SELECT
{fn CURRENT_TIMESTAMP()} AS 'Date & Time',
{fn CURRENT_DATE()} AS 'Date',
{fn CURRENT_TIME()} AS 'Time';
Result:
+-------------------------+------------+--------------+ | Date & Time | Date | Time | |-------------------------+------------+--------------| | 2018-06-13 21:57:29.197 | 2018-06-13 | 21:57:29.197 | +-------------------------+------------+--------------+
Also, CURRENT_TIMESTAMP() and CURRENT_TIME() both accept an optional argument to determine the seconds precision of the returned value.
Examples:
SELECT
{fn CURRENT_TIMESTAMP(3)} AS 'Date & Time',
{fn CURRENT_TIME(6)} AS 'Time';
Result:
+-------------------------+-----------------+ | Date & Time | Time | |-------------------------+-----------------| | 2018-06-13 22:00:59.263 | 22:00:59.263000 | +-------------------------+-----------------+
In this example, CURRENT_TIMESTAMP() returns a seconds precision of 3, while CURRENT_TIME() returns a seconds precision of 6.
Example 3 – CURDATE() and CURTIME()
You can use CURDATE() and/or CURTIME() as an alternative to the above date and time functions:
SELECT
{fn CURDATE()} AS 'Date',
{fn CURTIME()} AS 'Time';
Result:
+------------+--------------+ | Date | Time | |------------+--------------| | 2018-06-13 | 22:05:20.013 | +------------+--------------+
Note that these arguments don’t accept any arguments, so if you need to specify the seconds precision, use CURRENT_DATE() and/or CURRENT_TIME() instead.
Example 4 – Days and Weeks
There are a bunch of ODBC scalar functions for returning specific parts of a date value. These functions accept an argument, which is the date expression to extract the desired date part from.
Here are the various functions for returning the days and weeks part from the date:
DECLARE @date datetime2 = '2000-02-14 07:31:58.1234567';
SELECT
{fn DAYNAME(@date)} AS 'DAYNAME',
{fn DAYOFYEAR(@date)} AS 'DAYOFYEAR',
{fn DAYOFMONTH(@date)} AS 'DAYOFMONTH',
{fn DAYOFWEEK(@date)} AS 'DAYOFWEEK',
{fn WEEK(@date)} AS 'WEEK';
Result:
+-----------+-------------+--------------+-------------+--------+ | DAYNAME | DAYOFYEAR | DAYOFMONTH | DAYOFWEEK | WEEK | |-----------+-------------+--------------+-------------+--------| | Monday | 45 | 14 | 2 | 8 | +-----------+-------------+--------------+-------------+--------+
In this case, we assigned a date to a variable, then passed that variable to each function. The function then returned the applicable date part.
Example 5 – Months, Quarters, and Years
Here are the various functions for returning the months, quarters, and years part from the date/time value:
DECLARE @date datetime2 = '2000-02-14 07:31:58.1234567';
SELECT
{fn YEAR(@date)} AS 'YEAR',
{fn MONTH(@date)} AS 'MONTH',
{fn MONTHNAME(@date)} AS 'MONTHNAME',
{fn QUARTER(@date)} AS 'QUARTER';
Result:
+--------+---------+-------------+-----------+ | YEAR | MONTH | MONTHNAME | QUARTER | |--------+---------+-------------+-----------| | 2000 | 2 | February | 1 | +--------+---------+-------------+-----------+
Example 6 – Hours, Minutes, and Seconds
Here are the various functions for returning the hours, minutes, and seconds portion from the date/time value:
DECLARE @date datetime2 = '2000-02-14 07:31:58.1234567';
SELECT
{fn HOUR(@date)} AS 'HOUR',
{fn MINUTE(@date)} AS 'MINUTE',
{fn SECOND(@date)} AS 'SECOND';
Result:
+--------+----------+----------+ | HOUR | MINUTE | SECOND | |--------+----------+----------| | 7 | 31 | 58 | +--------+----------+----------+
Example 7 – The EXTRACT() Function
There’s also an ODBC scalar function called EXTRACT(), which allows you to extract the various date parts from the date/time value. Examples below.
Year, Month, and Day
DECLARE @date datetime2 = '2000-02-14 07:31:58.1234567';
SELECT
{fn EXTRACT(YEAR FROM @date)} AS 'YEAR',
{fn EXTRACT(MONTH FROM @date)} AS 'MONTH',
{fn EXTRACT(DAY FROM @date)} AS 'DAY';
Result:
+--------+---------+-------+ | YEAR | MONTH | DAY | |--------+---------+-------| | 2000 | 2 | 14 | +--------+---------+-------+
Hours, Minutes, and Seconds
DECLARE @date datetime2 = '2000-02-14 07:31:58.1234567';
SELECT
{fn EXTRACT(HOUR FROM @date)} AS 'HOUR',
{fn EXTRACT(MINUTE FROM @date)} AS 'MINUTE',
{fn EXTRACT(SECOND FROM @date)} AS 'SECOND';
Result:
+--------+----------+----------+ | HOUR | MINUTE | SECOND | |--------+----------+----------| | 7 | 31 | 58 | +--------+----------+----------+
Example 8 – The TIMESTAMPADD() Function
The TIMESTAMPADD() allows you to add a specified number of the specified interval (date/time part) to the date/time value. This function accepts three arguments; the interval (date/time part) to add (e.g. month), the number of that portion to add, and the date value. Examples below.
Year, Quarter, Month, and Day
DECLARE @date datetime2 = '2000-02-14 07:31:58.1234567';
SELECT
{fn TIMESTAMPADD(SQL_TSI_YEAR, 21, @date)} AS 'SQL_TSI_YEAR',
{fn TIMESTAMPADD(SQL_TSI_QUARTER, 21, @date)} AS 'SQL_TSI_QUARTER',
{fn TIMESTAMPADD(SQL_TSI_MONTH, 21, @date)} AS 'SQL_TSI_MONTH',
{fn TIMESTAMPADD(SQL_TSI_DAY, 21, @date)} AS 'SQL_TSI_DAY';
Result:
SQL_TSI_YEAR | 2021-02-14 07:31:58.1234567 SQL_TSI_QUARTER | 2005-05-14 07:31:58.1234567 SQL_TSI_MONTH | 2001-11-14 07:31:58.1234567 SQL_TSI_DAY | 2000-03-06 07:31:58.1234567
Hours, Minutes, and Seconds
DECLARE @date datetime2 = '2000-02-14 07:31:58.1234567';
SELECT
{fn TIMESTAMPADD(SQL_TSI_HOUR, 5, @date)} AS 'SQL_TSI_HOUR',
{fn TIMESTAMPADD(SQL_TSI_MINUTE, 5, @date)} AS 'SQL_TSI_MINUTE',
{fn TIMESTAMPADD(SQL_TSI_SECOND, 5, @date)} AS 'SQL_TSI_SECOND',
{fn TIMESTAMPADD(SQL_TSI_FRAC_SECOND, 5, @date)} AS 'SQL_TSI_FRAC_SECOND';
Result:
SQL_TSI_HOUR | 2000-02-14 12:31:58.1234567 SQL_TSI_MINUTE | 2000-02-14 07:36:58.1234567 SQL_TSI_SECOND | 2000-02-14 07:32:03.1234567 SQL_TSI_FRAC_SECOND | 2000-02-14 07:31:58.1284567
Example 9 – The TIMESTAMPDIFF() Function
The TIMESTAMPDIFF() function returns the difference between two date/time values. This function accepts three arguments; the interval (date/time part) for which to calculate the difference (e.g. month), the first date, and the second date. The function returns the number of intervals that the first date is greater than the second date Examples below.
Year, Quarter, Month, Week, and Day
DECLARE @date1 datetime2 = '2000-02-14 07:31:58.1234567';
DECLARE @date2 datetime2 = DATEADD(year, 1, @date1);
SELECT
{fn TIMESTAMPDIFF(SQL_TSI_YEAR, @date1, @date2)} AS 'SQL_TSI_YEAR',
{fn TIMESTAMPDIFF(SQL_TSI_QUARTER, @date1, @date2)} AS 'SQL_TSI_QUARTER',
{fn TIMESTAMPDIFF(SQL_TSI_MONTH, @date1, @date2)} AS 'SQL_TSI_MONTH',
{fn TIMESTAMPDIFF(SQL_TSI_WEEK, @date1, @date2)} AS 'SQL_TSI_WEEK',
{fn TIMESTAMPDIFF(SQL_TSI_DAY, @date1, @date2)} AS 'SQL_TSI_DAY';
Result:
+----------------+-------------------+-----------------+----------------+---------------+ | SQL_TSI_YEAR | SQL_TSI_QUARTER | SQL_TSI_MONTH | SQL_TSI_WEEK | SQL_TSI_DAY | |----------------+-------------------+-----------------+----------------+---------------| | 1 | 4 | 12 | 52 | 366 | +----------------+-------------------+-----------------+----------------+---------------+
Hours, Minutes, and Seconds
DECLARE @date1 datetime2 = '2000-02-14 07:31:58.1234567';
DECLARE @date2 datetime2 = DATEADD(day, 1, @date1);
SELECT
{fn TIMESTAMPDIFF(SQL_TSI_HOUR, @date1, @date2)} AS 'SQL_TSI_HOUR',
{fn TIMESTAMPDIFF(SQL_TSI_MINUTE, @date1, @date2)} AS 'SQL_TSI_MINUTE',
{fn TIMESTAMPDIFF(SQL_TSI_SECOND, @date1, @date2)} AS 'SQL_TSI_SECOND',
{fn TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND, @date1, @date2)} AS 'SQL_TSI_FRAC_SECOND';
Result:
+----------------+------------------+------------------+-----------------------+ | SQL_TSI_HOUR | SQL_TSI_MINUTE | SQL_TSI_SECOND | SQL_TSI_FRAC_SECOND | |----------------+------------------+------------------+-----------------------| | 24 | 1440 | 86400 | 86400000 | +----------------+------------------+------------------+-----------------------+