The transforms in this category manipulate date values:
| Transform | Description |
|---|---|
| age | Calculates an age and returns the result as a string such as "72 years, 3 months, and 18 days" |
| ageDays | Calculates an age and returns the number of days in the result as a string such as "18" |
| ageMonths | Calculates an age and returns the number of months in the result as a string such as "3" |
| ageYears | Calculates an age and returns the number of years in the result as a string such as "72" |
| date | Formats a date |
| dateAdd | Adds days, months, or years to a date |
| dateDay | Returns the day part of a date, a number from 1 to 31 |
| dateDifference | Returns the number of days between two dates |
| dateGtoJ | Converts a Gregorian date to a Julian date |
| dateJtoG | Converts a Julian date to a Gregorian date |
| dateJNYD | Specifies the Julian New Year's Day for subsequent conversions |
| dateModifier | Returns the modifier word from a date, such as "bef", "circa", etc. |
| dateMDSwap | Swaps the day and month value if they are ambiguous |
| dateMonth | Returns the month part of a date, a number from 1 to 12 |
| dateMonthAbbr | Returns the month part of a date converted to an abbreviated name, "Jan", "Feb", etc. |
| dateMonthName | Returns the month part of a date converted to a name, "January", "February", etc. |
| dateQuarter | Parses the input value to extract a quarter value (1,2,3,4) and a year, then formats the result with an optional argument to specify the format |
| dateSubtract | Subtracts days, months, or years from a date |
| dateYear | Returns the year part of a date with an optional argument to add or substract years |
| dayOfWeek | Returns the day of the week for a Gregorian date |
Non-English Dates
| Transform | Description |
|---|---|
| dateFrench | Converts a date that uses French month names/abbreviations to English and formats it according to the default date format. |
| dateGerman | Converts a date that uses German month names/abbreviations to English and formats it according to the default date format. |
| dateItalian | Converts a date that uses Italian month names/abbreviations to English and formats it according to the default date format. |
| dateSpanish | Converts a date that uses Spanish month names/abbreviations to English and formats it according to the default date format. |
date:f1:f2:f3
Formats a date value according to one of the given format parameters, "f1", "f2", or "f3".
Field Values
The field value must be a date recognized by ORA when it standardizes field values.
Date Format Parameters
Format parameters use keywords to indicate where to insert parts of the date.
f1 is a required parameter. If it is missing, the date value will not be transformed. If the date value has a year, month, and day, ORA uses f1 to format the value.
f2 is an optional parameter. If it is missing, it defaults to the f1 value. If the date value has a year and month, but no day, ORA uses f2 to format the value.
f3 is an optional parameter. If it is missing, it defaults to the f2 value. If the date value has a year, but no month and day, ORA uses f3 to format the value.
Format Parameter Keywords
The format specification may include the following keywords. The keywords are case-sensitive, so you must use the keywords below exactly as they appear. You can combine the keywords in any sequence. Any text in the format that does not match one of the keywords will be included as-is in the result.
- dd
- Replaced by a two-digit day number in the range 01 to 31. For days less than 10, the result will have a leading zero.
- d
- Replaced by a one- or two-digit day number. For days less than 10, the result will not have a leading zero.
- mm
- Replaced by a two-digit month number in the range 01 to 12. For months less than 10, the result will have a leading zero.
- m
- Replaced by a one- or two-digit month number. For months less than 10, the result will not have a leading zero.
- Mmmm
- Replaced by a mixed-case month name, "January", "February", etc.
- Mmm
- Replaced by a mixed-case month abbreviation, "Jan", "Feb", etc.
- MMMM
- Replaced by an upper-case month name, "JANUARY", "FEBRUARY", etc.
- MMM
- Replaced by an upper-case month abbreviation, "JAN", "FEB", etc.
- mmmm
- Replaced by an lower-case month name, "january", "february", etc.
- mmm
- Replaced by an lower-case month abbreviation, "jan", "feb", etc.
- yyyy
- Replaced by a four-digit year. For years less than 1000, the result will have a leading zero.
- yyy
- Replaced by a three- or four-digit year. For years less than 1000, the result will not have a leading zero.
Examples
- Format a date in year-month-day format where all the parts are digits:
[Birth Date:date:yyyy-mm-dd]If a field named Birth Date has the value "12 May 1952", the result is "
1952-05-12".If a field named Birth Date has the value "May 1952", the result is "
1952-05-00".If a field named Birth Date has the value "1952", the result is "
1952-00-00". - Format a date showing only the date parts that are present in the field value:
[Death Date:date:yyyy-mm-dd:yyyy-mm:yyyy]If a field named Death Date has the value "1977", the result is "
1977". - Format a date in "month day, year" format where the month is a mixed-case month name, not a number:
[Death Date:date:Mmmm d, yyy:Mmmm, yyy:yyy]If a field named Death Date has the value "14 December 1977", the result is "
December 14, 1977"".If a field named Death Date has the value "December 1977", the result is "
December, 1977"".If a field named Death Date has the value "1977", the result is "
1977".
Special Characters
If you want to use one of the keyword characters (d, m, M, or Y) in the format as a literal character, you must escape it with '\'. For example:
[Birth Date:date:\day\: d, \month\: m, \year\: yyy]Note that ":" is the parameter delimiter in transforms and it must be escaped in addition to the date formatting keyword characters.
Calendar Warning
Please see the Date Manipulation Notes page for important information about date field values.
age:birth-date
Calculates an age and returns the result as a string such as "72 years, 3 months, and 18 days".
The input value must be recognized as a date and must not include any modifier word such as "before", etc. The input value is assumed to be an event date, such as a marriage date, death date, etc.
The birth-date parameter must be recognized as a date and must not include any modifier word such as "before", etc.
The result is a text value in the form "n years, n months, and n days", where units are omitted if they are zero. If one or both of the dates are partial dates, such as "May 1940" which does not include the day of the month, the result is preceded by "~" to indicate "approximate".
For example, if the "Death Date" Field has the value "8 Sep 2022", and the "Birth Date" Field has the value "21 Apr 1926", the result of [Death Date:age:[Birth Date]] is "96 years, 4 months, and 18 days".
If the "Death Date" Field has the value "29 Aug 1915", and the "Birth Date" Field has the value "29 Aug 1982", the result of [Death Date:age:[Birth Date]] is "67 years". The month and day units are omitted because they are both zero.
ageDays:birth-date
Calculates an age and returns the number of days in the result as a string such as "18".
The input value must be recognized as a date and must not include any modifier word such as "before", etc. The input value is assumed to be an event date, such as a marriage date, death date, etc.
The birth-date parameter must be recognized as a date and must not include any modifier word such as "before", etc.
The result is a number. If one or both of the dates are partial dates, such as "May 1940" which does not include the day of the month, the result is preceded by "~" to indicate "approximate".
For example, if the "Death Date" Field has the value "8 Sep 2022", and the "Birth Date" Field has the value "21 Apr 1926", the result of [Death Date:ageDays:[Birth Date]] is "18".
ageMonths:birth-date
Calculates an age and returns the number of months in the result as a string such as "3".
The input value must be recognized as a date and must not include any modifier word such as "before", etc. The input value is assumed to be an event date, such as a marriage date, death date, etc.
The birth-date parameter must be recognized as a date and must not include any modifier word such as "before", etc.
The result is a number. If one or both of the dates are partial dates, such as "May 1940" which does not include the day of the month, the result is preceded by "~" to indicate "approximate".
For example, if the "Death Date" Field has the value "8 Sep 2022", and the "Birth Date" Field has the value "21 Apr 1926", the result of [Death Date:ageMonths:[Birth Date]] is "4".
ageYears:birth-date
Calculates an age and returns the number of years in the result as a string such as "72".
The input value must be recognized as a date and must not include any modifier word such as "before", etc. The input value is assumed to be an event date, such as a marriage date, death date, etc.
The birth-date parameter must be recognized as a date and must not include any modifier word such as "before", etc.
The result is a number. If one or both of the dates are partial dates, such as "May 1940" which does not include the day of the month, the result is preceded by "~" to indicate "approximate".
For example, if the "Death Date" Field has the value "8 Sep 2022", and the "Birth Date" Field has the value "21 Apr 1926", the result of [Death Date:ageYears:[Birth Date]] is "96".
dateAdd:delta
Adds days, months, or years to the current value.
The input value must be recognized as a date and must not include any modifier word such as "before", etc. The delta parameter must be one or more numbers followed by a suffix, "d" for days, "m" for months, or "y" for years. If a number has no suffix, "d" (days) is assumed.
If the input value includes a year only, ORA assumes January 1 for the month and day. If the input value includes a year and month, ORA assumes day 1.
The result is a date and it is formatted according to the current date format.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateAdd:1d] is "13 May 1953".
You may specify multiple numbers separated by a space.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateAdd:1y 1d] is "13 May 1954".
When you specify multiple numbers, the changes are applied in the same sequence as the numbers in the parameter.
When a number is negative, the result is the same as subtracting the number of days, months, or years.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateAdd:-3y] is "12 May 1950".
See the dateSubtract transform for another way to subtract values from a date.
Calendar Warning
Please see the Date Manipulation Notes page for important information about date field values.
dateAdd treats all dates as belonging to the Gregorian calendar.
When operating on Julian dates, the result will be correct except:
- when the interval spans the conversion from the Julian calendar to the Gregorian calendar,
- when the interval spans a year boundary and the starting day of the year is not January 1.
In the following examples, the Julian New Year's Day property is assumed to be March 25.
For example, in British North America 2 September 1752 (Julian) was followed by 14 September 1752 (Gregorian):
| Input Date | Transform | Output Date | Notes |
|---|---|---|---|
| 2 Sep 1752 | [Date:dateAdd:-1d] |
1 Sep 1752 | This is the correct result. Both dates are Julian. |
| 2 Sep 1752 | [Date:dateAdd:1d] |
3 Sep 1752 | This is an incorrect result. In British North America, 2 Sep 1752 was a Julian date, but there was no 3 Sep 1752. The next day was 14 Sep 1752, a Gregorian date. |
| 2 Sep 1752 | [Date:dateJtoG:dateAdd:1d] |
14 Sep 1752 | This is the correct result. |
Also, in British North America before the change to the Gregorian calendar, the civil year changed on March 25:
| Input Date | Transform | Output Date | Notes |
|---|---|---|---|
| 24 Mar 1751 | [Date:dateAdd:-1d] |
23 Sep 1751 | This is the correct result. |
| 24 Mar 1751 | [Date:dateAdd:1d] |
25 Sep 1751 | This is an incorrect result. In British North America, "24 Mar 1751" was followed by "25 Mar 1752". |
| 24 Mar 1751 | [Date:dateJtoG:dateAdd:1d:dateGtoJ] |
25 Mar 1752 | This is the correct result. |
dateDay
Returns the day part of a date, a number from 1 to 31. The input value must be a valid date with year, month, and day parts.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateDay] is "12".
dateDifference:date
Returns the number of days between two dates.
dateDifference subtracts the parameter (D2) from the Field value (D1), D1 - D2. The result will be positive if D1 > D2. The result will be negative if D2 < D1.
For example, if the "Death Date" field has the value "14 Dec 1977", and the "Burial Date" field has the value "17 Dec 1977" the result of [Death Date:dateDifference:[Burial Date]] is "-3". "D1" is Death Date and "D2" is Burial Date, and Death Date - Burial Date = -3.
If you want the result to be a positive number even if D2 is greater than D1, use the mathAbs transform: with the same values as the example above, the result of [Death Date:dateDifference:[Burial Date]:mathAbs] is "3".
dateGtoJ
Given a date using the Gregorian calendar, dateGtoJ converts the date to the Julian calendar.
The input value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc.
For example, if a field named Date has the value "15 OCT 1582", the result of [Date:dateGtoJ] is "5 OCT 1582".
Calendar Warning
Please see the Date Manipulation Notes page for important information about date field values.
ORA does not detect whether a date is a Gregorian date or Julian date. It's up to the user to decide based on the collection, time-period, etc.
Many locales did not use January 1 as the start of a new year when using the Julian calendar. When ORA converts the value, it uses the Julian New Year's Day property to determine the first day of the new year. If the Julian New Year's Day property is not correct for the locale of the current record, the dateGtoJ result will not be correct for some days of the year. See dateJNYD.
dateJtoG
Given a date using the Julian calendar, dateJtoG converts the date to the Gregorian calendar.
The input value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc.
For example, if a field named Date has the value "5 OCT 1582", the result of [Date:dateJtoG] is "15 OCT 1582".
Calendar Warning
Please see the Date Manipulation Notes page for important information about date field values.
ORA does not detect whether a date is a Gregorian date or Julian date. It's up to the user to decide based on the collection, time-period, etc.
Many locales did not use January 1 as the start of a new year when using the Julian calendar. When ORA converts the value, it uses the Julian New Year's Day property to determine the first day of the new year. If the Julian New Year's Day property is not correct for the locale of the current record, the dateJtoG result will not be correct for some days of the year. See dateJNYD.
dateJNYD:first-month:first-day
The dateJNYD transform does not modify the input value. It sets a temporary value for the Julian New Year's Day property. The temporary new year's day value influences subsequent date conversions in the same template.
If the value passed to the transform is not empty, and if the parameters are valid, the dateJNYD transform returns an empty value but produces a true result.
If the value passed to the transform is empty, or if the parameters are not valid, the dateJNYD transform returns an empty value and produces a false result.
The field value passed to the transform does not have to be a date. The value is not processed by the transform. It is only required because the transform infrastructure requires a field and the value must not be empty because no transforms are processed if the initial field value is empty.
Example
Several countries shifted the first day of the new year to January 1 before adopting the Gregorian calendar. For example, Spain changed the new year date to January 1 as of January 1, 1556 and changed to the Gregorian calendar in October, 1582, with its last Julian date being October 4, 1582. To convert a Julian date in the range "1 JAN 1556" through "4 OCT 1882" to a Gregorian date, the Julian New Year's Date must be set to January 1. If your Julian New Year's Day preference is set to a different date, the dateJtoG transform will not return the correct value.
For this example, assume that the Julian New Year's Day preference is set to "March 25". Also assume that date was recorded in Spain.
| Input Date | Transform | Output Date | Notes |
|---|---|---|---|
| 10 Mar 1556 | [Date:dateJtoG] |
20 Mar 1557 | This is not the correct result given the assumptions described above. |
| 10 Mar 1556 | [Date:dateJNYD:1:1][Date:dateJtoG] |
20 Mar 1556 | This is the correct result given the assumptions described above. |
Calendar Warning
Please see the Date Manipulation Notes page for important information about date field values.
dateMDSwap
Swaps the day and month value if they are ambiguous.
For example, if the "Date" field has the value "10/4/1912", ORA will usually interpret that date assuming an m/d/y sequence and the result is "4 October 1912". However, using [Date:dateMDSwap] the result is "10 April 1912".
dateMDSwap will return the input date unchanged if the date is unambiguous, i.e., if one of the values is greater than 12.
dateModifier
Returns the modifier word part of a date, a value such as "bef", "before", "aft", "after", "ca", "circa", etc. The input value must be a valid date.
For example, if the "Date" field has the value "after 12 May 1953", the result of [Date:datemodifier] is "after".
dateMonth
Returns the month part of a date, a number from 1 to 12. The input value must be a valid date with year and month parts.
For example, if the "Date" field has the value "25 June 1949", the result of [Date:dateMonth] is "6".
dateMonthAbbr
Returns the month part of a date as a month name abbreviation, "Jan", "Feb", etc. The input value must be a valid date with year and month parts.
For example, if the "Date" field has the value "25 June 1949", the result of [Date:dateMonthAbbr] is "Jun".
dateMonthName
Returns the month part of a date as a month name, "January", "February", etc. The input value must be a valid date with year and month parts.
For example, if the "Date" field has the value "25 June 1949", the result of [Date:dateMonthName] is "June".
dateQuarter:format
Parses the input value to extract a quarter value (1,2,3,4) and a year, then formats the result using the default Quarter Date Format or an optional argument to specify the format.
For example, if the "Quarter" field has the value "1897 Apr-May-Jun", the result of [Quarter:dateQuarter] is "Q2 1897". The actual result will vary according to the default Quarter Date Format because the optional format parameter was omitted.
If the "Quarter" field has the value "Apr-May-Jun 1875", the result of [Quarter:dateQuarter:Sss yyyy] is "Apr 1897" because the given format specifies the capitalized starting month (Sss) of the quarter.
If the Field includes a quarter value, or a year value, but not both, dateQuarter will return the input value unmodified.
Use the following codes to create quarter date formats.
| Code | Example Output |
Description |
|---|---|---|
cm |
8 | The one- or two-digit number of the middle month, "2", "5", "8", or "11". |
cmm |
08 | The two-digit number of the middle month, "02", "05", "08", or "11". |
d |
1 | The one-digit starting day of the month. Always "1". |
dd |
01 | The two-digit starting day of the month. Always "01". |
edd |
31 | The ending month's last day, 31 for March, 30 for June and September, 31 for December. |
eee |
sep | The three-letter abbreviation of the ending month, all lowercase. |
Eee |
Sep | The three-letter abbreviation of the ending month, first character capitalized. |
EEE |
SEP | The three-letter abbreviation of the ending month, all uppercase. |
eeee |
september | The full name of the ending month, all lowercase. |
Eeee |
September | The full name of the ending month, first character capitalized. |
EEEE |
SEPTEMBER | The full name of the ending month, all uppercase. |
em |
9 | The one- or two-digit number of the ending month, "3", "6", "9", or "12". |
emm |
09 | The two-digit number of the ending month, "03", "06", "09", or "12". |
mmm |
aug | The three-letter abbreviation of the middle month, all lowercase. |
Mmm |
Aug | The three-letter abbreviation of the middle month, first character capitalized. |
MMM |
AUG | The three-letter abbreviation of the middle month, all uppercase. |
mmmm |
august | The full name of the middle month, all lowercase. |
Mmmm |
August | The full name of the middle month, first character capitalized. |
MMMM |
AUGUST | The full name of the middle month, all uppercase. |
n |
3 | The quarter number, 1, 2, 3, or 4. |
sm |
7 | The one- or two-digit number of the starting month, "1", "4", "7", or "10". |
smm |
07 | The two-digit number of the starting month, "01", "04", "07", or "10". |
sss |
jul | The three-letter abbreviation of the starting month, all lowercase. |
Sss |
Jul | The three-letter abbreviation of the starting month, first character capitalized. |
SSS |
JUL | The three-letter abbreviation of the starting month, all uppercase. |
ssss |
july | The full name of the starting month, all lowercase. |
Ssss |
July | The full name of the starting month, first character capitalized. |
SSSS |
JULY | The full name of the starting month, all uppercase. |
yyy |
768 | The year, three or four digits, as required. |
yyyy |
0768 | The year, four digits, padded with a leading zero if necessary. |
dateSubtract:delta
Subtracts days, months, or years from the current value.
The input value must be recognized as a date and must not include any modifier word such as "before", etc. The delta parameter must be one or more numbers followed by a suffix, "d" for days, "m" for months, or "y" for years. If a number has no suffix, "d" (days) is assumed.
If the input value includes a year only, ORA assumes January 1 for the month and day. If the input value includes a year and month, ORA assumes day 1.
The result is a date and it is formatted according to the current date format.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateSubtract:1d] is "11 May 1953".
You may specify multiple numbers separated by a space.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateSubtract:1y 1d] is "11 May 1953".
When you specify multiple numbers, the changes are applied in the same sequence as the numbers in the parameter.
When a number is negative, the result is the same as adding the number of days, months, or years.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateSubtract:-3y] is "12 May 1956".
See the dateAdd transform for another way to add values to a date.
Calendar Warning
Please see the Date Manipulation Notes page for important information about date field values.
dateSubtract treats all dates as belonging to the Gregorian calendar. Please see the dateAdd transform for a discussion of the issues related to calculating dates that span the Julian-to-Gregorian calendar change.
dateYear:delta
Returns the year part of a date. The input value must be a valid date or quarter date.
The delta parameter is optional. If specified, it must be a number, and negative numbers must be preceded by "-". Positive numbers may be preceded by "+", but that is not required.
For example, if the "Date" field has the value "12 May 1953", the result of [Date:dateYear] is "1953" and the result of [Date:dateYear:-1] is "1952".
If the "Quarter Date" field has the value "Q1 1904", the result of [Quarter Date:dateYear] is "1904" and the result of [Quarter Date:dateYear:-1] is "1903".
The delta parameter is useful when creating query parameters for URLs to search a repository and you want to specify a range of years before and after the actual year.
dayOfWeek:version
Returns the day of the week for a Gregorian date. The current value must be a date with year, month, and day parts and must not include any modifier word such as "before", etc.
- If version is "
short", "s", or not specified, the result will be an abbreviated day name, "Sun", "Mon", "Tue", etc.For example, if the "Date" field has the value "12 May 1953", the result of
[Date:dayofweek]is "Tue". The result of[Date:dayofweek:s]is also "Tue" - If version is "
long" or "l", the result will be the full day name, "Sunday", "Monday", "Tuesday", etc.For example, if the "Date" field has the value "12 May 1953", the result of
[Date:dayofweek:l]is "Tuesday".The actual short and long day names and abbreviations are determined by your browser's current locale settings.
- If version is "
number" or "n", the result will be the day of the week number, as a digit, where Sunday is 0 (zero), Monday is 1 (one), etc.For example, if the "Date" field has the value "12 May 1953", the result of
[Date:dayofweek:n]is "2".
Calendar Warning
dayOfWeek treats all dates as belonging to the Gregorian calendar. The result will NOT be correct for Julian dates.
For example, the Julian date 2 September 1752 was a Wednesday. dayOfWeek will return Saturday, which is correct for a Gregorian calendar date, but incorrect for a Julian calendar date. You may determine the day of week for a Julian date by converting the date to Gregorian first.
For example, if the "Date" field has the value 2 SEP 1752, the result of [Date:dateJtoG:dayofweek:long] is "Wednesday".
Date Range
ORA's date manipulation transforms support dates from 1 January 0001 to dates well into the future. ORA's date manipulation transforms do not support BCE (BC) dates.
dateFrench
Converts a date that uses French month names/abbreviations to English and formats it according to the default date format.
For example, if the "Date de Naissance" Field has the value "4 Mars 1950", and the default date format is "d Mmmm yyy", the result of [Date de Naissance:dateFrench] is "4 March 1950".
If you want the resulting date in a different format, pass the result to the date transform. For example, if the "Date de Naissance" Field has the value "4 Mars 1950", the result of [Date de Naissance:dateFrench:date:d MMM yyy] is "4 MAR 1953".
dateGerman
Converts a date that uses German month names/abbreviations to English and formats it according to the default date format.
For example, if the "Geburtsdatum" Field has the value "4 März 1950", and the default date format is "d Mmmm yyy", the result of [Geburtsdatum:dateGerman] is "4 March 1950".
If you want the resulting date in a different format, pass the result to the date transform. For example, if the "Geburtsdatum" Field has the value "4 März 1950", the result of [Geburtsdatum:dateGerman:date:d MMM yyy] is "4 MAR 1950".
dateItalian
Converts a date that uses Italian month names/abbreviations to English and formats it according to the default date format.
For example, if the "Data di Nascita" Field has the value "4 marzo 1950", and the default date format is "d Mmmm yyy", the result of [Data di Nascita:dateItalian] is "4 March 1950".
If you want the resulting date in a different format, pass the result to the date transform. For example, if the "Data di Nascita" Field has the value "4 marzo 1950", the result of [Data di Nascita:dateItalian:date:d MMM yyy] is "4 MAR 1950".
dateSpanish
Converts a date that uses Spanish month names/abbreviations to English and formats it according to the default date format.
For example, if the "Fecha de Nacimiento" Field has the value "4 marzo 1950", and the default date format is "d Mmmm yyy", the result of [Fecha de Nacimiento:dateSpanish] is "4 March 1950".
If you want the resulting date in a different format, pass the result to the date transform. For example, if the "Fecha de Nacimiento" Field has the value "4 marzo 1950", the result of [Fecha de Nacimiento:dateSpanish:date:d MMM yyy] is "4 MAR 1950".