Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

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


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service