Date Validation Rules

About Date Validation Rules

Date Validation Rules can be used to cleanse any data that have been entered with a date format. The supported checks are:

  • Checking that the date is well formed so that it can be easily ingested into a data platform
  • Checking that the date is within a suitable time period

 

You can create a new data validation rule here, with the following basic options:

  • date_format - this specifies the date format, for example %Y-%m-%d %H:%M:%S
  • range_check - this specifies how the range check will be performed. Options are:
    • none - there is no range check and any date is accepted
    • fixed - the date must be between two fixed dates
    • rolling - the date is specified as an offset from time when the rule is run
  • range_minimum - this specifies the earliest date that iss accepted
  • range_maximum - the specifies the latest date that iss accepted

If the range_check is fixed then the range_minimum and range_maximum fields should be entered as dates in the format specifed in date_format.

If the range_check is rolling then the range_minimum and range_maximum fields should be specified as the number of days offset from the time when the rule is run.

Examples

To check dates of the form "2016-04-23 17:36:00" and check that they are all dates within the last seven days:

FieldValue
date_format %Y-%m-%d %H:%M:%S
range_checkrolling
range_minimum-7
range_maximum0

To check the dates that have the form "17:36 12/04/2017" and also check that they are all dates within the 1980s

FieldValue
date_format%H:%M %d/%m/%Y
range_checkfixed
range_minimum00:00 01/01/1980
range_maximum00:00 01/01/1990

Supported characters in the date_format field:

CodeMeaningExample
%aWeekday as abbreviated name of the locale.Mon
%AWeekday as full name of the locale.Monday
%wWeekday as a decimal number, where 0 is Sunday and 6 is Saturday.1
%dDay of the month as a zero-padded decimal number.30
%-dDay of the month as a decimal number. (Platform specific)30
%bMonth as abbreviated name of the locale.Sep
%BMonth as full name of the locale.September
%mMonth as a zero-padded decimal number.09
%-mMonth as a decimal number. (Platform specific)9
%yYear without century as a zero-padded decimal number.13
%YYear with century as a decimal number.2013
%HHour (24-hour clock) as a zero-padded decimal number.07
%-HHour (24-hour clock) as a decimal number. (Platform specific)7
%IHour (12-hour clock) as a zero-padded decimal number.07
%-IHour (12-hour clock) as a decimal number. (Platform specific)7
%pEquivalent of either AM or PM of the locale.AM
%MMinute as a zero-padded decimal number.06
%-MMinute as a decimal number. (Platform specific)6
%SSecond as a zero-padded decimal number.05
%-SSecond as a decimal number. (Platform specific)5
%fMicrosecond as a decimal number, zero-padded on the left.000000
%zUTC offset in the form +HHMM or -HHMM (empty string if the object is naive). 
%ZTime zone name (empty string if the object is naive). 
%jDay of the year as a zero-padded decimal number.273
%-jDay of the year as a decimal number. (Platform specific)273
%UWeek number of the year (Sunday as the first day of the week) as a zero padded decimal number. All days in a new year preceding the first Sunday are considered to be in week 0.39
%WWeek number of the year (Monday as the first day of the week) as a decimal number. All days in a new year preceding the first Monday are considered to be in week 0.39
%cAppropriate date and time representation of the locale.Mon Sep 30 07:06:05 2013
%xAppropriate date representation of the locale.09/30/13
%XAppropriate time representation of the locale.07:06:05
%%A literal '%' character.%