DateTime()

Format

DateTime(Arg1 [,FormatString])

Returns

Converts the supplied argument into a DateTime data type.

Inputs

Arg1: The argument to be converted.
Behaviour: Required
Dimension: Collection, SingleValue, MultiCollection
Valid data types: Decimal, Integer, String, DateTime, Duration

FormatString: The string specifying how to format the result.
Behaviour: Optional
Dimensions: SingleValue
Valid data types: String

How to format data types

Format Codes

The following codes within the FormatString are replaced with the relevant information. The codes are case sensitive.

• The codes must be in quotation marks. E.g. "Day"
• You may concatenate the codes to produce the desired string. E.g. "DayMonth"
• You may include other text, spaces, and punctuation within the quotation marks. E.g. "Day, Month"
• Any other text within the quotation marks is converted to upper case.

Standard Patterns

Pattern	Usage	Corresponds to custom pattern of...
G	This is the default pattern. Can only be used for parsing when specified in conjunction with a time zone provider.	yyyy '-' MM '-' dd 'T' HH ':' mm ':' ss z '('o<g>')
F	Can only be used for parsing when specified in conjunction with a time zone provider.	yyyy '-' MM '-' dd 'T' HH ':' mm ':' ss;FFFFFFF z '('o<g>')

Custom Patterns

Pattern	Meaning	Example
/	The date separator for the format provider; slash in the invariant culture.	en-US: yyyy/MM/dd → 2011/10/09 de-DE: yyyy/MM/dd → 2011.10.09
d or dd	Day of month (01 to 31). dd is zero-padded; d is not.	1st of the month: d → 1 (would parse "01" as well) dd → 01 21st of the month: d → 21 dd → 21
ddd	Abbreviated day-of-week name, not case-sensitive. When parsing, day of week is validated against the computed date, but does not affect the calculations of that date. This value is culture-sensitive.	February 4th 2012 (a Saturday) en-US: Sat fr-FR: sam.
dddd	Full day-of-week name, not case-sensitive. When parsing, the day of week is validated against the computed date, but does not affect the calculations of that date.	February 4th 2012 (a Saturday) en-US: Saturday fr-FR: samedi
M or MM	Month of year specified as a number (01-12). MM is zero-padded; M is not.	June: M → 6 MM → 06   December: M → 12 MM → 12
MMM	Abbreviated month name, not case-sensitive. This is culture-sensitive.	(In an English locale) June: MMM → Jun (can parse from "jun" or "JUN" etc.) December: MMM → Dec (can parse from "dec" or "DEC" etc.)
MMMM	Full month name, not case-sensitive. This is culture-sensitive.	(In an English locale) June: MMMM → June (can parse from "june" or "JUNE" etc.) December: MMMM → December (can parse from "december" or "DECEMBER" etc.)
y or yy	Two-digit absolute year with an optional leading - sign. A single y allows up to two digits to be parsed, but formats only one digit where possible. When parsing, the "base century" is chosen from the template value; if the two-digit year is greater than 30, the corresponding year in the previous century is used. Note that when formatting, no checking is performed to ensure that the year will be parsed to the same value. (For example, 1725 would be formatted as 25 but parsed as 2025.)	Assuming a template value of 2000 (the default): 2012: y → 12 2040: y → 40 - parsing "40" would give a date in 1940 2004: y → 4 2004: yy → 04
yyy	Three digit absolute year with optional leading - sign. This will parse up to five digits, but only format to as many as are required, with a minimum of three.	1984: parsed to 1984, formatted to 1984 00123: parsed to year 123, formatted just to 123
yyyy	The absolute year as 4 or 5 digits with an optional leading - sign. If the absolute year is outside the range [-9999, 9999] the value will be formatted (with the excess digit), but the result may not be parsed back to the original value. If the next character in the pattern represents a literal non-digit, or a non-alphanumeric character, or this appears at the end of the pattern, then up to five digits will be parsed. Otherwise, only exactly 4 digits will be parsed.
yyyyy	The absolute year as exactly 5 digits with an optional leading - sign.	2012: → 02012 12345: → 12345
Y, YY, YYY, YYYY or YYYYY	The year of era, zero-padded as necessary to the same number of characters as the number of 'Y' characters.	3 B.C.: YYYY → 0003
g or gg	The name of the era. This is calendar and culture specific.	13 B.C. (ISO calendar, en-US): Y g → 13 B.C.
:	The time separator for the format provider; colon in the invariant culture.	HH:mm → 07:30
;	This is always formatted as a period, but can parse either a period or a comma. In all other respects it behaves as the period custom specifier. The purpose of this specifier is to properly parse ISO-8601 times, where a comma is allowed as the separator for sub-second values.	Pattern ss;fff parses 53,123 and 53.123 identically.
.	This is always a period ("."); not a culture-sensitive decimal separator. The only difference between a period and any other literal character is that when followed by a series of "F" characters, the period will be removed if there are no fractional seconds.	12 seconds, 500 milliseconds (en-US): ss.FFF → 12.5 12 seconds, 500 milliseconds (fr-FR): ss.FFF → 12.5
H or HH	The hour of day in the 24-hour clock; a value 0-23. Note that when parsing local date/time values, a value of 24 may be exceptionally permitted to allow specification of a following day's midnight. E.g. The values 2012-11-24T24:00:00 and 2012-11-25T00:00:00 are equivalent. A value of 24 is never produced when formatting.	7.30am: H:mm → 7:30 H:mm → 19:30 HH:mm → 07:30 HH:mm → 19:30
h or hh	The hour of day in the 12-hour clock; a value 1-12. When parsing, if no am/pm designator is specified, the parsed value is in the morning.	7.30am: h:mm → 7:30 h:mm → 7:30 hh:mm → 07:30 hh:mm → 07:30
m or mm	Minutes within an hour (00 to 59). mm is zero-padded; m is not.	5 minutes: m:ss → 5:00 mm:ss → 05:00
s or ss	Seconds within a minute (00 to 59). ss is zero-padded; s is not.	5 seconds: s.fff → 5.000 ss.fff → 05.000
f, ff ... up to fffffff	The fractional second part of the time, using exactly the specified number of characters (up to 7, for a representation accurate to a tick).	1 second, 340 milliseconds: s.fff → 1.340 s.ff → 1.34 s.f → 1.3
F, FF ... up to FFFFFFF	The fractional second part of the offset, using at most the specified number of characters (up to 7, for a representation accurate to a tick). Trailing digits are truncated towards zero, and trailing insignificant zeroes are truncated. If this comes after a period (".") and the value is zero, the period is also truncated.	1 second, 340 milliseconds: s.FFF → 1.34 s.FF → 1.34 s.F → 1.3   Exactly 1 second: s.F → 1
t or tt	The culture-specific AM/PM designator, either in full (for tt) or just the first character (for t).	13:10: h:mm tt → 1:10 PM h:mm:sst → 1:10:00P
o	Offset specifier.  Note that a pattern without an offset specifier will always lead to potential data loss when used with time zones which aren't a single fixed offset, due to the normal issues of time zone transitions (typically for daylight saving time). If the pattern does contain an offset specifier, then when parsing, the offset present in the text is validated against the time zone. By specifying both a time zone identifier and an offset, the ambiguity around time zone transitions is eliminated. The "o" specifier must always be followed by a pattern for offset within angle brackets. The pattern may be a standard pattern or a custom pattern.	A pattern of "yyyy-MM-dd HH:mm:ss o<G>" might produce output of 2013-07-17 06:20:35 Z or 2013-07-17 07:20:35 +01:00
z	Used to parse or format a time zone identifier. The UTC+/-xx:xx format for fixed offset time zones is always valid, regardless of provider. A null provider can be specified, in which case the pattern can only be used for formatting. If the pattern does not contain the z specifier, the time zone from the default value is used. The standard patterns all use a default value with the UTC time zone.
x	Used only for formatting; it includes the abbreviation associated with the time zone at the given time, such as "PST" or "PDT". This is format-only as abbreviations are often ambiguous; they are not a substitute for full time zone identifiers. Note that time zones from the TZDB provider only have abbreviations; time zones from the BCL provider have the standard/daylight names provided by TimeZoneInfo. For example, in the London time zone in summer, this will format as "BST" when using the TZDB-based zone, and "GMT Summer Time" when using the BCL-based zone.

Other special characters available in duration types:

Character	Meaning	Example
%	Escape to force a single-character custom pattern to be treated as such.	%H → 5
'	Open and close a text literal, which can include double quotes.	HH'H'mm'M' → 07H30M
"	Open and close a text literal, which can include single quotes.	HH"'"mm → 07'30
\	Escapes the following character.	HH\'mm → 07'30

Examples

Expression: DateTime(12) Result: Returns the integer value of 12 as a DateTime value of "12". E.g. 12

Expression: DateTime(12.2) Result: Returns the decimal value of 12.2 as a DateTime value of "12.2". E.g. 12.2

Expression: DateTime("00:00:00 01-Jan-2016", "dd/MM/yy HH:mm:ss")  Result: Format the time in the supplied format. E.g. 01/01/16 00:00:00

Expression: DateTime({du'-01:02:03.456'}, "HH:mm:ss:fffffff") Result: Format the supplied duration as a DateTime with the specified format. E.g. 01:02:03:456

Release History

• DateTime() 4.6.5
  • Changed Arg1 parameter to support MultiCollection values.
• DateTime() 4.0
  • Initial version