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)