New Columns
String to Date
Turn weird date formats into date/datetime formats
The String to Date tool supports a quite narrow functionality. It is used to turn custom date column into date/datetime data formats. Generally Less is relatively good at autodetecting date formats with the Change Columns, but sometimes date formats require manual parsing. That’s where the String to Date tool is useful.
Syntax
You tell Less how to parse you date by refering to certain formats. You can use the syntax below.| Format | Description |
|---|---|
%a | Weekday as locale’s abbreviated name: Sat, Sun |
%A | Weekday as locale’s full name: Saturday, Sunday |
%w | Weekday as a decimal number where 0 is Sunday |
%d | Day of the month as a decimal number: 01,02, …, 31 |
%b | Month as locale’s abbreviated name: Jan, Feb |
%B | Month as locale’s full name: January, February |
%m | Month as a zero-padded decimal number: 01,02,…,12 |
%y | Year without century as decimal number: 00,01, …,99 |
%Y | Year with century as a decimal number: 2011, 2012 |
%H | Hour (24-hour clock) as decimal number: 00,01, …, 23 |
%I | Hour (12-hour clock) as decimal number: 01, 02, …, 12 |
%p | Locale’s equivalent of either AM or PM: AM, am, pm, PM |
%M | Minute as a zero-padded decimal number.: 00, 01, …, 59 |
%S | Second number (00–60), zero-padded to 2 digits |
%f | Microsecond as a 6 digits number: 000000, 000001, …, 999999 |
%z | UTC offset in the form ±HHMM[SS[.ffffff]]: +0000, -0400, +1030 |
%Z | Time zone name: UTC, GMT |
%j | Day of the year as a decimal number: 001, 002, …, 366 |
%U | Week number of the year (Sunday as the first day of the week: 00, 01, …, 53 |
%W | Week number of the year (Monday as the first day of the week): 00, 01, …, 53 |
Configuration
The String to Date tool consists of three required input.1
Input Column Name
The String to Date tool will create a new column. Use this input to name it.
2
Select Column to Parse
Select the column with the date that you want to parse.
3
Input the Format of Your Columns
Input the format of your date column using the syntax options above. Check out the example below to see how.
When To Use
It’s relatively easy to know when you should use the String to Date tool. You have a date column that is formatted as a string and want to turn it into a date/datetime data format. You have tried using the Change Columns to turn it into a date/datetime, but it failed. In that case, you should use the String to Date tool.Examples
Strange Column to Date format
Strange Column to Date format
I have a custom date column that I need to transform to a date date format
The
%b corresponds to Jan/Feb/Mar, the %d corresponds to the day of the month and the %Y corresponds to the year.Note how we are connecting these with the actual symbols from your column - i.e. - and ,.