Text Field Format Specifications

When using the Update Column (ToDate) function, you must specify a text field containing time information. To ensure correct conversion results, the text format in the field needs to follow specific rules.

Date and Time Format Specifiers

The time information contained in the text has specified value ranges. Values exceeding these ranges will not produce correct update results.

Format Specifier
Format Type
Remarks
yyyy
Four-digit year
 None
MM
Two-digit month
 Range: 01-12 (01=January)
M
Single-digit month
 None
dd
Two-digit day
 Range: 01-31
d
Single-digit day
 None
hh
Two-digit hour
 Range: 01-24. Use AM/PM suffixes to distinguish between morning and afternoon times. E.g., 10:05:29PM will be converted to 22:05:29.
mm
Two-digit minute
 Range: 00-59
ss
Two-digit second
 Range: 00-59
T
Time separator
 Used to concatenate date and time values

Text Formats

Common text formats and special considerations are described below:

  • With delimiters

Supported delimiters include: space, comma (,), slash (/), backslash (\), hyphen (-), colon (:). Different delimiter types can be mixed.

Text Format
Supported
Remarks
yyyy[delimiter]MM[delimiter]dd[delimiter]hh[delimiter]mm[delimiter]ss
√
Year-month-day-hour-minute-second can be omitted from right to left sequentially, but middle components cannot be omitted. E.g., "year-month-hour-minute-second" cannot be recognized.
MM[delimiter]dd[delimiter]hh[delimiter]mm[delimiter]ss
√
Year can be omitted. The current year will be used by default.
yyyy[delimiter]M[delimiter]d
√
E.g., 2023-9-1 will be converted to 2023/09/01.
yyyy[delimiter]M[delimiter]
√
If only year and single-digit month exist, the date will be updated to yyyy/0M/01.
dd[delimiter]MM[delimiter]yyyy
√
With delimiters, reverse order input (day-month-year) is supported.
  • Without delimiters
Text Format
Supported
Remarks
yyyyMMddhhmmss
√
Components can be omitted from right to left sequentially, but middle components cannot be omitted.
MMddThhmmss
√
When year is omitted without delimiters, "T" must be used to connect date and time for proper recognition.
yyyyMd
×
Single-digit month/day formats are not supported without delimiters.
yyyyM
√
Only year and single-digit month: will update to yyyy/0M/01.
ddMMyyyy
×
Reverse order input (day-month-year) is not supported without delimiters.

Related Topics

Update Column (ToDate)