Date format
Change one or more columns from Text to Date or from Date to Text.
Change one or more columns from Text to Date or from Date to Text.
When to use this tool
Use when you want to change a date column to text or change a text column to a date.
Input
The Date format tool requires one data input.
Configuration
Use the following configuration options to help create your Date format configuration.
In , add your data source.
Click + Tool.
In the search bar, search for Date format. Click + Add tool.
Tip
You can also find the Date format tool in the Organize section.
Connect the tool to your data set.
In the configuration pane, enter the following information:
Table 61. Date format tool configurationField
Description
Target column
Select the column to change, from a date column to text or change a text column to a date.
New format
Select the date format to use:
dd-mm-yy
dd-mm-yyyy
mm-dd-yyyy
yyyy-mm-dd
Custom
Note
If you select Custom, you must enter the custom format in the field. For more information, see Custom date format pattern.
Custom date format pattern
Use the following list of format codes that can be used for a custom pattern:
Code | Description | Example |
---|---|---|
| This code abbreviates the weekday names. | Sun, Mon, ..., Sat |
| This code formats weekday names as the full name. | Sunday, Monday, ..., Saturday |
| This code formats the day of the month as a number. | 01, 02, ..., 31 |
| This code abbreviates the month name. | Jan, Feb, ..., Dec |
| This code formats the month names as the full name. | January, February, ..., December |
| This code formats a year with a century. | 19 |
| This code formats the date and time in en_US, based on locale. | Fri 21 Apr 2023 10:55:21 AM Eastern European Summer Time |
| his code formats the short date, equivalent to %m/%d/%y in en_US. | 07/30/22 |
| This code formats the short date, equivalent to %Y-%m-%d in en_US. | 2023-04-20 |
| This code formats the hour in a 24-hour format. | 00-23 |
| This code abbreviates the month format. | Sep |
| This code formats the hour in a 12-hour format. | 01-12 |
| This code formats the day of the year. | 145 |
| This code formats the hour in a 24-hour format. | 0-23 |
| This code formats the milliseconds, to three digits. | 707 |
| This code formats the time to the minute. | 52 |
| This code formats the month as a number. | 01, 02, ..., 12 |
| This code adds newline character. (`\n') | (`\n') |
| This code formats the month as an ordinal. | 1st, 2nd, 3rd, … |
| This code formats an am or pm selection in uppercase. | AM, PM |
| This code formats an am or pm selection in lowercase. | am, pm |
| This code formats 12-hour time, equivalent to %H:%M in en_US, based on locale. | 02:45:02 PM |
| This code formats 12-hour time, equivalent to %I:%M:%S %p in en_US, based on locale. | 02:45:02 PM |
| This code formats the second in two digits (00-60). | 06 |
| This code formats Unix time. | 1455803239 |
| This code formats the users time. | 08:47:03 |
| This code adds the horizontal-tab character (`\t`). | (`\t`) |
| This code formats the week number, with Sunday as the first day of the week, in two digits. (00-53) | 35 |
| This code formats the weekday number, with Monday as the first day as 1 (1-7) | 3 |
| This code formats the short date, equivalent to %e-%b-%Y in en_US, based on locale. | 21-Apr-2023 |
| This code formats the week number, with Monday as the first day of week one, in two digits. (00-53) | 45 |
| This code formats the weekday, with Sunday as the first day of the week. (0-6) | 5 |
| This code formats the time, equivalent to %T or %r in en_US, based on locale. | 12:53:32 |
| his code formats the date, equivalent to %D in en_US, based on locale. | 04/20/23 |
| This code formats a year without the century. | 00, 01, ..., 99 |
| This code formats a year with the century. | 1999, 2000, ..., 2020 |
| This code formats time zone name, replaced with an empty string if it is not found. | Eastern European Summer Time |
| This code formats the time zone offset from UTC, with a leading plus sign for UTC and ones east of UTC. Leading minus sign for zones west of UTC. Hours and minutes follow. | +0300 |
%a, %b, %d
formats to Sun-Jan-01
.
Salesforce export usage
If you want to modify the date column and use it for export to Salesforce, you must use a specific format. Salesforce supports the following format: yyyy-mm-ddT00:00:00.000000Z
.
Since the data set needs to be exported to Salesforce, if you use the Date format tool, ensure that configuration is custom: %Y-%m-%dT00:00:00.000000Z
.
You can also leave the original format as-is if you don't want to use the Date format tool to format the date.