Help Centre

Working with dates
Dates can come in a range of formats: ‘month-day-year’, ‘year/month/day’, etc. A common scenario is to ingest dates from a variety of sources, cleanse and standardise them, perform calculations using the dates, then output the results to a different format for compatibility with a downstream system (risk model, data warehouse, etc).
Quantemplate uses the Basic ISO date format
yyyymmdd
for all date calculations and transformations.

Columns formatted as dates in Excel files (.xlsx, .xlsm, .xls) are automatically converted to Basic ISO format on uploading to Quantemplate (these can then be checked in the Date Cleanse operation). All other date formats, including dates from CSV file imports, need to be converted using the Date Cleanse operation.
There are three groups of date functionality in Quantemplate:
  • operation converts dates to Quantemplate’s standard format (Basic ISO:
    yyyymmdd
    ), removes invalid dates and replaces them with blank values so that the columns can be used in date functions in Calculate. Invalid dates can be automatically captured in a Validation Report by enabling the validation option in the operation. This operation should be used before any date calculations are performed, typically in the earlier stages.
  • in Calculate perform calculations and transformations on cleansed dates
    (
    DATECALC
    ,
    DURATION
    ,
    ENDOFMONTH
    , etc).
  • operation formats dates for downstream systems, such as a data warehouse or risk model. Once dates have been reformatted in the Date Output operation they become text values and can no longer be used in date functions. This operation should be used after all required date calculations have been performed.

Supported date formats

Dates are values using components (days, months, quarters, years) and separators
./- 
to refer to a specific day.

Components supported by date operations

Component
Example
Description
d
9
One-digit day
dd
09
Two-digit day
ddd
Mon
Short day name (Date Output only)
dddd
Monday
Long day name (Date Output only)
m
4
One-digit month
mm
04
Two-digit month
mmm
Apr
Short month name
mmmm
April
Long month name (Date Output only)
yy
80
Short year
yyyy
1980
Long year
Qq
Q1
Quarter with prefix Q
.
.
Separator .
-
-
Separator -
/
/
Separator /
 
Separator ‘space’
*
.-/␣
Any separator (Date Cleanse only)

Examples

Format
Example
dd/mm/yyyy
29/06/2012
mm-dd-yyyy
06-29-2012
yyyymmdd
20120629
Qq-yyyy
Q1-2016
mmmm yyyy
June 2016

Number of components

Date Cleanse can interpret dates that have:
  • Three components: a year, a month and a day.
  • Two components: a year combined with a month or quarter. These are known as partial dates and need to be converted to a specific day.
Values which comprise only a four-digit year are not supported as dates, but the general functions in Calculate and Validation should be sufficient for dealing with them.
Date Output supports any number or combination of components.

Quarters

A common date component is the Quarter, usually indicated by the quarter prefix
Q
followed by digits 1, 2, 3 or 4, e.g.
2016Q3
,
Q1-2017
.
Quantemplate supports the quarter prefix
Q
. The quarter-detection in Date Cleanse is not case-sensitive:
q1
and
Q1
will both be recognised as quarters. If your data requires support for a different quarter prefix, contact support@quantemplate.com.

Valid date ranges

The Date Cleanse operation will count dates between the years 1000 and 2999 as valid. If you need to check that dates fall within a more specific range, create a rule in the Validation operation.

Legacy date operations

The following script operations will soon be deprecated:
Once deprecated, it will no longer be possible to add these operations to a pipeline. They will continue to work in existing pipelines. Updating existing pipelines to remove legacy date operations is advised.