Converts text or numeric time values to date format

1.1 Problem description

Users record time in a variety of formats, some store it as a text field or a numeric field. Sometimes it is necessary to parse a string or numeric field into a date field.

1.2 Implementation idea

Use the self-service dataset to add a new column and add the "TODATE" function or "DATE" function to convert a text field or a numeric field into a field of time type.

2. Convert text fields to the date type field

Example data: text field.xlsx

1) Examples of the field formats for conversion are "2020/05/22", "2020-05-22", "200522", and "200520", as shown below.

2）Create a new self-help data set, check text field 1, 2, 3, 4, as shown below.

3）Add "New column", enter the formula TODATE(text field1) to turn text field 1 into a date field, and name the New column, click "OK", as shown in the figure below.

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Similarly, create New column and enter the formula TODATE(text field2), TODATE(text field3, "yyyyMMdd"), TODATE(text field4, "yyyyMM") respectively, as shown in the following figure.

Note: text field 4 only has month and year, after converting to date type, the default is the 1st of each month.

For detailed usage of the TODATE function, please see: TODATE Function

3. Convert numeric fields to the date type field

Example data: value field.xlsx

The field format for the example conversion is shown below.

In FineBI the following figure shows.

3.1 Single column numeric field to date field

The "year-month-day" field and the "A" field can also be converted into date fields by the "TODATE function". See Section 2 of this article for details.

Note: A field is a timestamp, fields in the FineDB table are saved with this type of A field.

The final result is shown in the following figure.

3.2 Convert numeric dates saved in multiple columns to date fields

You can use the "DATE function" to process multiple fields into a single date field if you want to save the year, month, and day as separate fields.

This section uses the Year and Month fields as examples.

1）Create a new self-service dataset and select the fields "year " and "month", as shown below.

2）New column "multi-column date merge", using the formula DATE (year, month, 22), as shown in the following figure.