TENOR2DATE
Updated 2023-10-13 14:04:51.410000
Syntax
SELECT [westclintech].[wct].[TENOR2DATE](
<@Tenor, nvarchar(4000),>
,<@StartDate, datetime,>
,<@SpotDate, datetime,>
,<@Holidays, nvarchar(max),>)
Description
Use the scalar function TENOR2DATE to convert an alphanumeric expression into a swaps or money market maturity date. These alphanumeric expressions consist of a number and a code identifying the date part: 'D' for days; 'W' for weeks; 'M' for months; and 'Y' for years. You can also use the codes 'ON' for overnight, 'TN' for tom/next and 'SN' for spot/next.
The calculation of the tenor abbreviation into a date is based on the spot date and the holidays supplied to the function. For monthly and yearly tenor codes, the modified following date roll rule is used—if the calculated date falls on a non-business date, then the date is advanced to the next business date unless that date is in the next calendar month in which case the date reverts to the last business date of the month.
Arguments
@SpotDate
the date from which all the maturity dates are calculated. @SpotDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@Holidays
the concatenated string of non-business dates (other than weekends) to be used in the date calculations.
@Tenor
the tenor code identifying the maturity date for the deposit or swap.
@StartDate
the date from the spot date is calculated. @StartDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
Return Type
datetime
Remarks
If @StartDate is NULL, @StartDate =GETDATE().
If @SpotDate is NULL, then @SpotDate is calculated as 2 business days from the @StartDate.
Use the NBD function to create the concatenated string of non-business dates.
Examples
In this example we calculate the maturity dates associated with a variety of tenors as of 2013-03-25.
SELECT tenor,
wct.TENOR2DATE( tenor, --@Tenor
'2013-03-25', --@StartDate
NULL, --@SpotDate
'' --@Holidays
) as mDate
FROM
(
SELECT 'ON'
UNION ALL
SELECT 'TN'
UNION ALL
SELECT 'SN'
UNION ALL
SELECT '1W'
UNION ALL
SELECT '2W'
UNION ALL
SELECT '1M'
UNION ALL
SELECT '3M'
UNION ALL
SELECT '6M'
UNION ALL
SELECT '1Y'
UNION ALL
SELECT '2Y'
UNION ALL
SELECT '5Y'
UNION ALL
SELECT '10Y'
) n(tenor);
This produces the following result.
{"columns":[{"field":"tenor"},{"field":"mDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"tenor":"ON","mDate":"2013-03-26 00:00:00.000"},{"tenor":"TN","mDate":"2013-03-27 00:00:00.000"},{"tenor":"SN","mDate":"2013-03-28 00:00:00.000"},{"tenor":"1W","mDate":"2013-04-03 00:00:00.000"},{"tenor":"2W","mDate":"2013-04-10 00:00:00.000"},{"tenor":"1M","mDate":"2013-04-29 00:00:00.000"},{"tenor":"3M","mDate":"2013-06-27 00:00:00.000"},{"tenor":"6M","mDate":"2013-09-27 00:00:00.000"},{"tenor":"1Y","mDate":"2014-03-27 00:00:00.000"},{"tenor":"2Y","mDate":"2015-03-27 00:00:00.000"},{"tenor":"5Y","mDate":"2018-03-27 00:00:00.000"},{"tenor":"10Y","mDate":"2023-03-27 00:00:00.000"}]}
Notice in the previous example that we did not enter the spot date and that we entered empty string for the holidays. The function automatically calculated the spot date as 27-Mar-2013 and only considered Saturday and Sunday to be non-business days.
In the following SQL, we calculate the holidays for the next 10 years, use the aggregate function NBD to create the holiday string, and pass the holiday string into the function. Normally, these holidays would be stored in a table, but for purposes of this example, we will just create them on the fly.
DECLARE @hol as varchar(max)
=
(
SELECT wct.NBD(hdate)
FROM
(
SELECT CASE DATEPART(dw, wct.CALCDATE(k.seriesvalue, 1, 1))
WHEN 1 THEN
DATEADD(d, 1, wct.CALCDATE(k.seriesvalue, 1, 1))
WHEN 7 THEN
DATEADD(d, -1, wct.CALCDATE(k.seriesvalue, 1, 1))
ELSE
wct.CALCDATE(k.seriesvalue, 1, 1)
END,
wct.FIRSTWEEKDAY(wct.CALCDATE(k.seriesvalue, 1, 1), 'Mon')
+ 14,
wct.FIRSTWEEKDAY(wct.CALCDATE(k.seriesvalue, 2, 1), 'Mon')
+ 14,
wct.LASTWEEKDAY(wct.CALCDATE(k.seriesvalue, 5, 1), 'Mon'),
CASE DATEPART(dw, wct.CALCDATE(k.seriesvalue, 7, 4))
WHEN 1 THEN
DATEADD(d, 1, wct.CALCDATE(k.seriesvalue, 7, 4))
WHEN 7 THEN
DATEADD(d, -1, wct.CALCDATE(k.seriesvalue, 7, 4))
ELSE
wct.CALCDATE(k.seriesvalue, 7, 4)
END,
wct.FIRSTWEEKDAY(wct.CALCDATE(k.seriesvalue, 9, 1), 'Mon')
,
wct.FIRSTWEEKDAY(wct.CALCDATE(k.seriesvalue, 10, 1), 'Mon')
+ 7,
wct.FIRSTWEEKDAY(wct.CALCDATE(k.seriesvalue, 11, 1), 'Thu')
+ 21,
CASE DATEPART(dw, wct.CALCDATE(k.seriesvalue, 12, 25))
WHEN 1 THEN
DATEADD(d, 1, wct.CALCDATE(k.seriesvalue, 12, 25))
WHEN 7 THEN
DATEADD(d, -1, wct.CALCDATE(k.seriesvalue, 12, 25)
)
ELSE
wct.CALCDATE(k.seriesvalue, 12, 25)
END
FROM wctMath.wct.SeriesInt(2013, 2023, NULL, NULL, NULL) k
) n([New Year's], [MLK Day], [President's Day], [Memorial Day],
[Independence Day], [Labor Day], [Columbus Day], [Thanksgiving]
, [Christmas])
CROSS APPLY
(
VALUES
('New Year', [New Year's]),
('MLK Day', [MLK Day]),
('Presidents Day', [President's Day]),
('Memorial Day', [Memorial Day]),
('Independence Day', [Independence Day]),
('Labor Day', [Labor Day]),
('Columbus Day', [Columbus Day]),
('Thanksgiving', [Thanksgiving]),
('Christmas', [Christmas])
) x (holiday, hdate)
);
SELECT tenor,
wct.TENOR2DATE(tenor, '2013-03-27', NULL, @hol) as mDate
FROM
(
SELECT 'ON'
UNION ALL
SELECT 'TN'
UNION ALL
SELECT 'SN'
UNION ALL
SELECT '1W'
UNION ALL
SELECT '2W'
UNION ALL
SELECT '1M'
UNION ALL
SELECT '3M'
UNION ALL
SELECT '6M'
UNION ALL
SELECT '1Y'
UNION ALL
SELECT '2Y'
UNION ALL
SELECT '5Y'
UNION ALL
SELECT '10Y'
) n(tenor);
This produces the following result.
{"columns":[{"field":"tenor"},{"field":"mDate","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"tenor":"ON","mDate":"2013-03-28 00:00:00.000"},{"tenor":"TN","mDate":"2013-03-29 00:00:00.000"},{"tenor":"SN","mDate":"2013-04-01 00:00:00.000"},{"tenor":"1W","mDate":"2013-04-05 00:00:00.000"},{"tenor":"2W","mDate":"2013-04-12 00:00:00.000"},{"tenor":"1M","mDate":"2013-04-29 00:00:00.000"},{"tenor":"3M","mDate":"2013-06-28 00:00:00.000"},{"tenor":"6M","mDate":"2013-09-30 00:00:00.000"},{"tenor":"1Y","mDate":"2014-03-31 00:00:00.000"},{"tenor":"2Y","mDate":"2015-03-30 00:00:00.000"},{"tenor":"5Y","mDate":"2018-03-29 00:00:00.000"},{"tenor":"10Y","mDate":"2023-03-29 00:00:00.000"}]}