SQL Server date conversion for swap maturities

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'UNIONALL

SELECT'TN'UNIONALL

SELECT'SN'UNIONALL

SELECT'1W'UNIONALL

SELECT'2W'UNIONALL

SELECT'1M'UNIONALL

SELECT'3M'UNIONALL

SELECT'6M'UNIONALL

SELECT'1Y'UNIONALL

SELECT'2Y'UNIONALL

SELECT'5Y'UNIONALL

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.