TENOR2DATE
Updated: 29 Mar 2013
Use 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.
Syntax
SELECT [wctFinancial].[wct].[TENOR2DATE](
<@Tenor, nvarchar(4000),>
,<@StartDate, datetime,>
,<@SpotDate, datetime,>
,<@Holidays, nvarchar(max),>)
Arguments
@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.
@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.
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.
Example
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.
tenor mDate
----- -----------------------
ON 2013-03-26 00:00:00.000
TN 2013-03-27 00:00:00.000
SN 2013-03-28 00:00:00.000
1W 2013-04-03 00:00:00.000
2W 2013-04-10 00:00:00.000
1M 2013-04-29 00:00:00.000
3M 2013-06-27 00:00:00.000
6M 2013-09-27 00:00:00.000
1Y 2014-03-27 00:00:00.000
2Y 2015-03-27 00:00:00.000
5Y 2018-03-27 00:00:00.000
10Y 2023-03-27 00:00:00.000
Notice in the previous example that we did not enter the spot date and that we entered emptry 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.
tenor mDate
----- -----------------------
ON 2013-03-28 00:00:00.000
TN 2013-03-29 00:00:00.000
SN 2013-04-01 00:00:00.000
1W 2013-04-05 00:00:00.000
2W 2013-04-12 00:00:00.000
1M 2013-04-29 00:00:00.000
3M 2013-06-28 00:00:00.000
6M 2013-09-30 00:00:00.000
1Y 2014-03-31 00:00:00.000
2Y 2015-03-30 00:00:00.000
5Y 2018-03-29 00:00:00.000
10Y 2023-03-29 00:00:00.000