Randomize DateTime Mask

The Randomize DateTime mask will randomize the Day and optionally the Month of date type fields.

This is particularly useful when you want to ensure that implied date boundaries are observed such as ensuring that randomized dates stay within the same month or the same year.

For example, suppose that your organization applies different tariffs based upon the month of the year. Therefore, in order to maintain consistency with other dependent data, you want to randomize only the Day part while preserving the Month and Year parts of such date fields.

The Year part is always preserved and that the Day part is always randomized.

The Month can be randomized or preserved.

Dates generated by this mask shall always be valid calendar dates and observe leap years.

There are two versions of this mask. One that operates on SQL Date/Time data types and one that operates on JSON date/time fields.

SQL Date/Time data types

This mask can only be used with SQL date and datetime data type fields. i.e. not with string types and not with time types because time types do not contain a date part.
  

Specifically, the supported types are:

MySQL: DATE, DATETIME, TIMESTAMP

Oracle: DATE, TIMESTAMP, TIMESTAMP WITH..TIME ZONE

SQL Server: date, datetime, datetime2, datetimeoffset, smalldatetime
  

NULLs are always preserved.

Hour, Minute, Second and Fraction Fields

Each of the Hour, Minute, Second and Fraction parameter fields shall be displayed in the panel only if the underlying data type supports such a field.

For example, none of these parameter fields shall be displayed for SQL Server "date" type. Another example, the Fraction parameter field shall not be displayed for Oracle "DATE" type.

Each of these parameters can either be 'Preserve' or 'Value'. If 'Value' is selected then an input field for a value specification shall be displayed.

The valid Hour value range is from 0 to 23 inclusive.

The valid Minute value range is from 0 to 59 inclusive.

The valid Second range is from 0 to 59 inclusive.

The valid Fraction range depends on the underlying DBMS data type.

JSON date/time fields

This version of the mask is used within the JSON mask to mask date/time string fields in JSON records.

It is similar to the regular SQL version of this mask except that it adds a Format field to define how the date/time value is to be parsed.
  

Format

The Format parameter is shown in the Randomize DateTime mask when used within a parent JSON mask.

This parameter defines the pattern of how string values are to be parsed. e.g. which character positions are to be interpreted as years, months, day, etc.

Oracle

The pattern symbols are as defined for Joda-Time DateTimeFormat class. These are mostly compatible with JDK date patterns.

Further details can be found at https://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
  

The count of pattern letters determine the format. The following descriptions refer to entries in the Presentation column in the table above:

text: If the number of pattern letters is 4 or more, the full form is used; otherwise a short or abbreviated form is used if available.

number: The minimum number of digits. Shorter numbers are zero-padded to this amount. When parsing, any number of digits are accepted.

year: Numeric presentation for year and weekyear fields are handled specially. For example, if the count of 'y' is 2, the year will be displayed as the zero-based year of the century, which is two digits.

month: 3 or over, use text, otherwise use number.

millis: The exact number of fractional digits. If more millisecond digits are available then specified the number will be truncated, if there are fewer than specified then the number will be zero-padded to the right. When parsing, only the exact number of digits are accepted.

zone: 'Z' outputs offset without a colon, 'ZZ' outputs the offset with a colon, 'ZZZ' or more outputs the zone id.

Time zone names ('z') cannot be parsed.

Any characters in the pattern that are not in the ranges of ['a'..'z'] and ['A'..'Z'] will be treated as quoted text. For instance, characters like ':', '.', ' ', '#' and '?' will appear in the resulting time text even they are not embraced within single quotes.

Examples of some common format patterns for Oracle when used within a JSON mask:
   

SQL Server

Date and time format string for JSON, as implemented by DataVeil on SQL Server, follow the .NET date/time format definitions. These are described in detail at https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

Format patterns are case sensitive.

Examples of some common format patterns for SQL Server when used within a JSON mask:
  

Splitter and XML date/time fields 

The version of the mask that is used within a Splitter or XML mask to mask date/time string fields is similar to the version used within a JSON mask.

The only difference is that the date and time format strings for the Format field are somewhat different. Specifically, the format strings correspond to the implementation on the relevant DBMS as described below.
  

MySQL

Please refer to MySQL documentation of the Date format specifiers. Currently these can be found at https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

Internally, DataVeil shall call the MySQL function DATE_FORMAT using the Format string that is specified in this mask.

Example of a common format pattern for MySQL when used within a Splitter mask:
  

Oracle

Please refer to Oracle's documentation of the 'Datetime Format Models'. Currently these can be found at https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Format-Models.html

Internally, DataVeil shall call the Oracle function TO_TIMESTAMP_TZ() using the Format string that is specified in this mask.

Examples of some common format patterns for Oracle when used within a Splitter or XML mask:
  

SQL Server

The mask's Format parameter for SQL Server takes the form:

[ input-style-num  | ]  output-format-pattern  [ | output-culture ]
  

Note: In most cases, only output-format-pattern needs to be specified. The other parameters, input-style-num and output-culture, are usually not required and should only be specified for unusual date/time patterns where the masked values generated are not as expected.
 

input-style-num

DataVeil relies on the TSQL CONVERT() function to parse an original string date/time value into an internal binary datetime format.

This parameter is optional because SQL Server can usually properly interpret input date/time strings automatically. If an input-style-num is specified then the pipe separator character '|' must follow it.

If DataVeil is reporting errors or the masked values are not as expected then it is suggested to specify the relevant input-style-num as described in the SQL Server documentation for 'CAST and CONVERT (Transact-SQL)', section 'Date and Time Styles'. Currently this can be found at https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Example: The input-style-num for the date/time string 2019-08-23T13:39:17.090 is 126; however, this input style is common and will be automatically recognized and so it is not actually required to specify this style number. See below for further examples.
 

output-format-pattern

DataVeil relies on the TSQL FORMAT() function to generate the string representation of the masked date/time value that shall be written to the masked XML record.

The output-format-pattern follows the .NET date/time format definitions. These are described in detail at https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

This parameter is mandatory. Format patterns are case sensitive.
 

output-culture

This parameter can explicitly specify the culture of the output formatting to be used. If this parameter is omitted then the culture of the current session is used as described for the TSQL FORMAT() function at https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

This parameter is optional. If specified, this value must be preceded by the pipe separator character '|'.

Example: 

The Format parameter for date/time strings such as 2019-08-23T13:39:17.090 can be entered with all parameters as shown here:

However, as in most typical use cases, only the output-format-pattern is required and so the Format parameter as shown below would be sufficient:

Examples of this mask's Format parameter for SQL Server when used within a Splitter or XML mask:
   

Deterministic

When this mask is used in deterministic mode then the same masked value shall always be generated for a given input value.

All mappings are 1-to-1 which means there are no collisions. i.e. Two different input values will always generate two distinct values.

Non-Deterministic  

When this mask is used in non-deterministic mode then different masked values are likely to be generated for each occurrence of a given input value, even during the course of a single masking run.

Mappings are many-to-many which means that collisions are possible. i.e. Two different input values may generate the same masked value.