Date

Generates calendar dates, with no time component.

Use it for dates such as a date of birth, an invoice date, a contract start or a reporting day. mode sets how the values are distributed:

One row for each consecutive day, as in a ledger, is Sequential.
Dates spread evenly across a range, such as birth dates, is Random.
Dates from a fixed list, such as quarter ends, is Lookup.
Dates concentrated around a target date, such as renewals before a deadline, is Clustered.

For a date with a time of day, use DateTime, or Timestamp for a value with a UTC offset. For a duration rather than a point in the calendar, use Interval.

Sub-field: value

Every JSON block on this page is a single field object, one entry in a table's fields array. See Building a Request for the full request structure.

Properties

Property	Type	Default	Description
`cultureLong`	`string?`	none	A culture name, such as `fr-FR`, whose long date format is used for the value. `en-US` produces `Friday, March 15, 2024`. Produces text. Cannot be combined with `exactFormat` or `cultureShort`. See Formatting the output.
`cultureShort`	`string?`	none	A culture name, such as `fr-FR`, whose short date format is used for the value. `en-US` produces `3/15/2024`. Produces text. Cannot be combined with `exactFormat` or `cultureLong`. See Formatting the output.
`exactFormat`	`string?`	none	A custom date format string, such as `MMM d, yyyy`. Produces text. Cannot be combined with `cultureShort` or `cultureLong`. See Formatting the output.
`lookupValues`	`LookupValue[]`	none	The dates for `Lookup` mode. Required in that mode.
`map`	`object`	required	Maps the `value` sub-field to a destination column. See Building a Request and Formatting the output.
`max`	`string?` (date)	`9999-12-31`	Inclusive upper bound. `Random` and `Clustered` only.
`mean`	`string?` (date)	none	The center of the distribution in `Clustered` mode. Required in that mode.
`min`	`string?` (date)	`0001-01-01`	Inclusive lower bound. `Random` and `Clustered` only. Must not be later than `max`.
`mode`	`"Sequential"` / `"Random"` / `"Lookup"` / `"Clustered"`	`"Sequential"`	How values are generated. See Modes.
`nullPercentageMax`	`number`	`0`	Upper bound (0-100) of the percentage of rows that should be null. See `nullPercentageMin`.
`nullPercentageMin`	`number`	`0`	Lower bound (0-100) of the percentage of rows that should be null. The rate is fixed once for the whole field from this range, not recalculated per row. Set equal to `nullPercentageMax` for an exact rate. Has no effect in `Sequential` mode.
`percentInRange`	`number?`	none	Percentage (0-100) of rows generated from `percentMin` to `percentMax` instead of the full range. `Random` only, and rejected in any other mode. See Random within a narrower window.
`percentMax`	`string?` (date)	none	Upper bound of the window `percentInRange` applies to. Required with `percentInRange`, and must be within `min` to `max`.
`percentMin`	`string?` (date)	none	Lower bound of the window `percentInRange` applies to. Required with `percentInRange`, and must be within `min` to `max`.
`seed`	`integer`	random	Per-field seed, overriding the request's own `seed` for this field only.
`start`	`string?` (date)	`0001-01-01`	The first row's value. `Sequential` only.
`stdDev`	`number?`	none	The standard deviation of the distribution in `Clustered` mode, in days. Required in that mode, and must be greater than 0.
`stepDays`	`integer?`	`1`	The number of days between rows in `Sequential` mode. `0` repeats `start`, and a negative value counts down. Must be a whole number.
`unique`	`boolean`	`false`	Whether every generated value must differ from the others. See Unique dates.
`weekDays`	`string[]?`	every day	The days of the week a value can be on. See Restricting days of the week. Rejected in `Lookup` mode.

Modes

Sequential increases by a fixed number of days. The first row is start, and each row after it is stepDays later. The values are not random, so the same request always produces the same dates, with or without a seed.
Random generates each row independently, with every date from min to max equally likely. percentInRange concentrates a percentage of the rows in a narrower window.
Lookup selects from the dates in lookupValues, optionally weighted. It never produces a date that is not in the list. See LookupValue.
Clustered generates a normal distribution around mean, with stdDev setting its width. See Clustered distribution.

Properties by mode

A property that applies only to another mode is ignored, except percentInRange, which is rejected outside Random, and weekDays, which is rejected in Lookup.

Property	Sequential	Random	Lookup	Clustered
`start`, `stepDays`	Yes
`min`, `max`		Yes		Yes
`percentInRange`, `percentMin`, `percentMax`		Yes
`lookupValues`			Yes
`mean`, `stdDev`				Yes
`weekDays`	Yes	Yes		Yes
`exactFormat`, `cultureShort`, `cultureLong`	Yes	Yes	Yes	Yes
`nullPercentageMin`, `nullPercentageMax`		Yes	Yes	Yes

Sequential mode never produces nulls. Use another mode for a column with null values.

Each mode checks its own settings before any row is generated, so a missing mean or an empty lookupValues list is rejected immediately.

LookupValue

Lookup mode selects from a list of dates. Each entry is an object with a value and an optional weight:

Property	Type	Default	Description
`value`	`string` (date)	required	The date.
`weight`	`number`	`1`	The entry's weight relative to the other entries in the list. Must be greater than 0.

Weights are relative, not percentages, so they do not need to total any particular number. 3 and 1 give the same distribution as 30 and 10: the first entry is selected three times as often as the second. With no weights set, every entry is equally likely.

The list must contain at least one entry.

A quarter-end date, with the last quarter twice as likely as the others:

{
  "type": "Date",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "2024-03-31" },
    { "value": "2024-06-30" },
    { "value": "2024-09-30" },
    { "value": "2024-12-31", "weight": 2 }
  ],
  "seed": 1,
  "map": { "value": { "column": "period_end", "dataType": "Date" } }
}

Sample output:

period_end
2024-06-30
2024-03-31
2024-09-30
2024-12-31
2024-12-31
2024-09-30

The weights total 5, so December 31 is selected for about two rows in five, and each other date for about one row in five. Each row is selected separately, so the proportions are approximate.

The same list with no weights:

{
  "type": "Date",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "2024-01-01" },
    { "value": "2024-07-04" },
    { "value": "2024-12-25" }
  ],
  "seed": 1,
  "map": { "value": { "column": "holiday", "dataType": "Date" } }
}

Sample output:

holiday
2024-01-01
2024-01-01
2024-07-04
2024-12-25
2024-07-04
2024-07-04

Clustered distribution

How mean, stdDev, min and max affect the output. The curve is the number of rows with each value. Most values are near mean, and fewer are further away, at a rate set by stdDev. A value beyond min or max is set to that boundary, shown by the solid bar at each one.

mean sets the center and stdDev sets the width, both in the field's own units. About 68% of values are within one stdDev of mean, about 95% within two, and about 99.7% within three. A larger stdDev spreads the values further from mean, and a smaller one keeps them closer to it.

min and max are optional. Without them, a value can be any distance from mean within the range the field supports. With them, a value outside the range is set to the nearer of min or max. It is not generated again. The solid bars in the diagram are these values.

When the range is narrow relative to stdDev, many rows have a value of exactly min or max. In the diagram, the range is one and a half standard deviations either side of mean. To reduce the number of rows on the boundaries, widen the range or reduce stdDev.

For a Date field, mean is a date and stdDev is a number of days. With a stdDev of 5, about 95% of rows are within 10 days of mean. With a stdDev of 90, about 95% are within 180 days.

Random within a narrower window

percentInRange generates a percentage of the rows of a Random field from a narrower window, and the rest from the full range. percentMin and percentMax set the window, which must be within min to max.

For each row, the field first selects either the window, with a probability of percentInRange, or the full range from min to max. It then generates a date from the selected range, with every date equally likely.

The rows generated from the full range can also be in the window, so the proportion of rows in the window is higher than percentInRange. The difference depends on the size of the window relative to the full range. With percentInRange set to 80, about four rows in five are generated from the window, and some of the remaining rows are in the window too.

Use it for a distribution weighted toward recent dates that still includes older ones.

Restricting days of the week

weekDays limits the days of the week a value can be on. Days are written by name, such as Monday, and matched without regard to case. If set, the list must contain at least one day.

How an excluded day is handled depends on the mode:

Sequential skips excluded days, so every row has a different date. A daily sequence of weekdays produces Friday, Monday, Tuesday.
Random and Clustered generate a new value until it is on an allowed day, so every value is between min and max. A range with no allowed day in it is rejected before any data is generated.
Lookup rejects weekDays, because every value is written exactly as listed. List only dates on the days you want instead.

In Sequential mode, when stepDays is a multiple of 7, including 0, every row is on the same day of the week as start. That day must be one of the allowed days, or the request is rejected.

Unique dates

With unique set, every row has a different date. A request for more rows than the field can produce is rejected before any data is generated, stating how many are available:

Mode	Distinct dates available
`Sequential`	One per row, or only one when `stepDays` is `0`.
`Random`	The number of days from `min` to `max`, counting only allowed days when `weekDays` is set.
`Clustered`	The same as `Random` when both `min` and `max` are set. Otherwise there is no fixed limit.
`Lookup`	The number of different dates in `lookupValues`.

With exactFormat, cultureShort or cultureLong set, two dates can produce the same text, so there is no fixed limit, and a request that runs out of distinct values is rejected during generation.

In Clustered mode, dates far from mean are rare. A unique request close to the limit can run out of distinct values during generation, even when the limit is not exceeded.

Formatting the output

Without a formatting property, value is a date and must be mapped to a Date column. The date is written in the request's csvFormat.dateFormat. See CSV formatting.

exactFormat, cultureShort and cultureLong produce text instead, and a field that sets one must be mapped to a text column such as VarChar. Only one of the three can be set.

exactFormat takes a custom date format string. The output is the same for every request. See Custom date and time format strings for the format specifiers.
cultureShort and cultureLong take a culture name and write the date in that culture's short or long date format. Use them for dates in the format of a particular country or language. See Culture names for how the names are formed. An unknown culture name is rejected.

Examples

Each example below is a single field object, one entry in a table's fields array within a full request, not a complete request on its own. See Building a Request for how it fits into a full DatasetRequest.

One row per calendar day

{
  "type": "Date",
  "mode": "Sequential",
  "start": "2024-01-01",
  "map": { "value": { "column": "report_date", "dataType": "Date" } }
}

Sample output:

report_date
2024-01-01
2024-01-02
2024-01-03
2024-01-04
2024-01-05

Every other week, counting backward

A negative stepDays counts down from start.

{
  "type": "Date",
  "mode": "Sequential",
  "start": "2024-06-28",
  "stepDays": -14,
  "map": { "value": { "column": "sprint_start", "dataType": "Date" } }
}

Sample output:

sprint_start
2024-06-28
2024-06-14
2024-05-31
2024-05-17
2024-05-03

Birth dates for adults

{
  "type": "Date",
  "mode": "Random",
  "min": "1950-01-01",
  "max": "2005-12-31",
  "seed": 3,
  "map": { "value": { "column": "date_of_birth", "dataType": "Date" } }
}

Sample output:

date_of_birth
1966-03-15
1981-04-14
1957-05-13
1969-05-03
1957-03-28

Mostly recent, with some older records

About four rows in five are generated from 2024, and the rest from the whole range, which also includes 2024.

{
  "type": "Date",
  "mode": "Random",
  "min": "2014-01-01",
  "max": "2024-12-31",
  "percentInRange": 80,
  "percentMin": "2024-01-01",
  "percentMax": "2024-12-31",
  "seed": 3,
  "map": { "value": { "column": "last_seen", "dataType": "Date" } }
}

Sample output:

last_seen
2024-04-11
2024-09-14
2014-12-03
2022-07-04
2024-09-12
2024-09-19

Business days only, formatted as text

The sequence starts on a Thursday and skips the weekend.

{
  "type": "Date",
  "mode": "Sequential",
  "start": "2024-01-04",
  "weekDays": ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
  "cultureLong": "en-US",
  "map": { "value": { "column": "business_day", "dataType": "VarChar" } }
}

Sample output:

business_day
Thursday, January 4, 2024
Friday, January 5, 2024
Monday, January 8, 2024
Tuesday, January 9, 2024
Wednesday, January 10, 2024
Thursday, January 11, 2024

Random weekend dates

weekDays also applies in Random and Clustered modes.

{
  "type": "Date",
  "mode": "Random",
  "min": "2024-03-01",
  "max": "2024-03-31",
  "weekDays": ["Saturday", "Sunday"],
  "seed": 2,
  "map": { "value": { "column": "event_date", "dataType": "Date" } }
}

Sample output:

event_date
2024-03-10
2024-03-03
2024-03-24
2024-03-23
2024-03-09

A custom text format

{
  "type": "Date",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-12-31",
  "exactFormat": "MMM d, yyyy",
  "seed": 3,
  "map": { "value": { "column": "printed_on", "dataType": "VarChar" } }
}

Sample output:

printed_on
Apr 2, 2024
Jun 27, 2024
Feb 12, 2024
Apr 20, 2024

A culture's short date format

{
  "type": "Date",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-12-31",
  "cultureShort": "fr-FR",
  "seed": 4,
  "map": { "value": { "column": "date_facture", "dataType": "VarChar" } }
}

Sample output:

date_facture
15/10/2024
28/01/2024
29/08/2024
29/10/2024

Due dates clustered around a target

{
  "type": "Date",
  "mode": "Clustered",
  "mean": "2024-07-01",
  "stdDev": 5,
  "min": "2024-06-01",
  "max": "2024-08-01",
  "seed": 3,
  "map": { "value": { "column": "due_date", "dataType": "Date" } }
}

Sample output:

due_date
2024-06-27
2024-07-10
2024-07-07
2024-06-30
2024-07-04
2024-06-26

Null rows

{
  "type": "Date",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-12-31",
  "nullPercentageMin": 30,
  "nullPercentageMax": 30,
  "seed": 4,
  "map": { "value": { "column": "closed_on", "dataType": "Date" } }
}

Sample output:

closed_on
2024-07-29


2024-07-14
2024-10-05
2024-06-27

Table of Contents

Date

Properties

Modes

Properties by mode

LookupValue

Clustered distribution

Random within a narrower window

Restricting days of the week

Unique dates

Formatting the output

Examples

One row per calendar day

Every other week, counting backward

Birth dates for adults

Mostly recent, with some older records

Business days only, formatted as text

Random weekend dates

A custom text format

A culture's short date format

Due dates clustered around a target

Null rows