Skip to main content

Varicent ELT Help Center

Date format

Abstract

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.

Configuring the Date format tool
  1. In , add your data source.

  2. Click symon_add_icon.png + Tool.

  3. In the search bar, search for Date format. Click + Add tool.

    Tip

    You can also find the Date format tool in the Organize section.

  4. Connect the tool to your data set.

  5. In the configuration pane, enter the following information:

    Table 61. Date format tool configuration

    Field

    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:

Table 62. Custom date format

Code

Description

Example

%a

This code abbreviates the weekday names.

Sun, Mon, ..., Sat

%A

This code formats weekday names as the full name.

Sunday, Monday, ..., Saturday

%d

This code formats the day of the month as a number.

01, 02, ..., 31

%b

This code abbreviates the month name.

Jan, Feb, ..., Dec

%B

This code formats the month names as the full name.

January, February, ..., December

%C

This code formats a year with a century.

19

%c

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

%D

his code formats the short date, equivalent to %m/%d/%y in en_US.

07/30/22

%F

This code formats the short date, equivalent to %Y-%m-%d in en_US.

2023-04-20

%H

This code formats the hour in a 24-hour format.

00-23

%h

This code abbreviates the month format.

Sep

%I

This code formats the hour in a 12-hour format.

01-12

%j

This code formats the day of the year.

145

%k

This code formats the hour in a 24-hour format.

0-23

%L

This code formats the milliseconds, to three digits.

707

%M

This code formats the time to the minute.

52

%m

This code formats the month as a number.

01, 02, ..., 12

%n

This code adds newline character. (`\n')

(`\n')

%o

This code formats the month as an ordinal.

1st, 2nd, 3rd, …

%p

This code formats an am or pm selection in uppercase.

AM, PM

%P

This code formats an am or pm selection in lowercase.

am, pm

%R

This code formats 12-hour time, equivalent to %H:%M in en_US, based on locale.

02:45:02 PM

%r

This code formats 12-hour time, equivalent to %I:%M:%S %p in en_US, based on locale.

02:45:02 PM

%S

This code formats the second in two digits (00-60).

06

%s

This code formats Unix time.

1455803239

%T

This code formats the users time.

08:47:03

%t

This code adds the horizontal-tab character (`\t`).

(`\t`)

%U

This code formats the week number, with Sunday as the first day of the week, in two digits. (00-53)

35

%u

This code formats the weekday number, with Monday as the first day as 1 (1-7)

3

%v

This code formats the short date, equivalent to %e-%b-%Y in en_US, based on locale.

21-Apr-2023

%W

This code formats the week number, with Monday as the first day of week one, in two digits. (00-53)

45

%w

This code formats the weekday, with Sunday as the first day of the week. (0-6)

5

%X

This code formats the time, equivalent to %T or %r in en_US, based on locale.

12:53:32

%x

his code formats the date, equivalent to %D in en_US, based on locale.

04/20/23

%y

This code formats a year without the century.

00, 01, ..., 99

%Y

This code formats a year with the century.

1999, 2000, ..., 2020

%Z

This code formats time zone name, replaced with an empty string if it is not found.

Eastern European Summer Time

%z

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



Example 2. Example of a custom pattern

%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.