ToDate / String Layer

Convert string values to dates using specified format. Similar to Python's datetime.strptime().date() or Rust's chrono::NaiveDate::parse_from_str(). Creates a date column without time components.

Example conversions:

Input String	Format Pattern	Result
2024-01-31	%Y-%m-%d	2024-01-31
31/01/2024	%d/%m/%Y	2024-01-31
01/31/2024	%m/%d/%Y	2024-01-31
Jan 31, 2024	%b %d, %Y	2024-01-31
Wednesday, January 31, 2024	%A, %B %d, %Y	2024-01-31
20240131	%Y%m%d	2024-01-31

Common applications:

Standardizing date formats across datasets
Converting textual dates for chronological analysis
Preparing data for date-based operations and filtering
Processing historical records and events
Creating date hierarchies for reporting (year, month, day)
Harmonizing dates from international sources
Enabling date-based joins between tables

Date-specific format specifiers compatible with Rust's chrono library:

Year specifiers:

%Y - Full 4-digit year (2024)
%y - 2-digit year (24) - interpreted as 2024, not 1924
%C - Century (20)

Month specifiers:

%m - Month number, zero-padded (01-12)
%b or %h - Abbreviated month name (Jan)
%B - Full month name (January)

Day specifiers:

%d - Day of month, zero-padded (01-31)
%e - Day of month, space-padded ( 1-31)
%j - Day of year, zero-padded (001-366)

Weekday specifiers:

%a - Abbreviated weekday name (Mon)
%A - Full weekday name (Monday)
%w - Weekday, 0-based from Sunday (0-6)
%u - Weekday, 1-based from Monday (1-7)

Week specifiers:

%U - Week number starting with Sunday (00-53)
%W - Week number starting with Monday (00-53)
%V - ISO week number (01-53)

Combined format shortcuts:

%F - ISO 8601 date format (equivalent to %Y-%m-%d)
%D - US date format (equivalent to %m/%d/%y)
%x - Locale's date representation

Most common real-world date formats:

ISO 8601: %Y-%m-%d (2024-01-31)
US format: %m/%d/%Y (01/31/2024)
UK/European: %d/%m/%Y (31/01/2024)
Continental European: %d.%m.%Y (31.01.2024)
Written style: %B %d, %Y (January 31, 2024)
Abbreviated: %b %d, %Y (Jan 31, 2024)
Database style: %Y%m%d (20240131)
Slash-separated: %Y/%m/%d (2024/01/31)
Dashed US: %m-%d-%Y (01-31-2024)

Note: This function processes date information only. For datetime values, use StringToDateTime instead. Weekday information (if present) is used for validation but not for determining the date.

Table

Select

column

The string column containing date text. Values not matching the format will become null.

Format

string

Date format pattern using strftime syntax compatible with Rust's chrono library.

Standard date formats:

%Y-%m-%d - ISO 8601 date format (2024-01-31)
%F - ISO 8601 date format shorthand (2024-01-31)
%D - US date format shorthand (01/31/24)

Regional date formats:

%m/%d/%Y - US format (01/31/2024)
%d/%m/%Y - UK/European format (31/01/2024)
%d.%m.%Y - Continental European format (31.01.2024)

Written date formats:

%B %d, %Y - Month name, day, year (January 31, 2024)
%A, %B %d, %Y - Weekday, month, day, year (Wednesday, January 31, 2024)
%a, %b %d, %Y - Abbreviated names (Wed, Jan 31, 2024)

Compact formats:

%Y%m%d - Numeric concatenated (20240131)
%y%m%d - Short year concatenated (240131)

If not provided, the system attempts to infer the format from data patterns.

Exact

bool

true

Controls format matching behavior:

true: String must exactly match format (2024-01-31 for %Y-%m-%d)
false: Format can match within text (Date: 2024-01-31 End)

Name for the new column. If not provided, the system generates a unique name. If AsColumn matches an existing column, the existing column is replaced. The name should follow valid column naming conventions.