Format date (formatdate)
Transform a timestamp value into a string using a format pattern you specify, optionally specifying a different time zone to see the date in one different than yours and a language locale to adapt it. This is the inverse of the Parse date (parsedate) operation.
How does it work in the search window?
Select Create column in the search window toolbar, then select the Format date operation. You need to specify at least two arguments:
|Argument||Data type||More information|
You can either select a column with that data type or introduce it manually.
In case you want to introduce it, note that this value should be a date: Year-Month_Day Hour:Minute:Second.Millisecond (yyyy-MM-dd HH:mm:ss.SSS) → You can skip milliseconds.
|Date format (mandatory)||string||Must use the notation for valid format patterns described below.|
You need a valid string format the app can recognize so it returns meaningful results. If you leave the field empty or introduce a value the app cannot recognize, the default Time Zone is UTC. You can use one of the following methods:
Be aware that some of the codes coincide, as CST could mean Central Standard Time or China Standard Time. In that case, it would be advisable to avoid using codes and introduce any of the other formats mentioned.
Applies the correct language to the day part of the date based on the locale you specify. If this argument is not added, the day will be in your own locale, which is determined by your user preferences.
The accepted formats for the locale argument correspond to the ISO 639-1 codes, which consist of two-character codes ("en" for English, "es" for Spanish, "fr" for Frech or "de" for German), or a combination of this with the ISO 3166-2, which consists of two-character codes for specific countries ("gb" for United Kingdom, "es" for Spain...) and together they result in a four-character code specifying the subdivision ("en-us" for American English, etc.).
It must not be confused with the ISO codes that consist of three-character codes.
As not all codes are recognized as valid locales, entering an invalid code will return the locale set in your user preferences. Check the full list of locales here.
The data type of the values in the new column is string.
In the Date format argument, enter the exact format of the strings to be converted using the following patterns.
|Type||Valid format patterns||Example|
|Year||YY, YYYY||19, 2019|
|Month||M, MM, MMM, MMMM||7, 07, Jul, July|
|Day in month||D, DD||9, 09|
|Hour (0...23, 00...23)||H, HH||7, 07|
|Hour (1...12, 01...12)||h, hh||7, 07|
|Minute||m, mm||3, 03|
|Second||s, ss||8, 08|
|Weekyear (15, 2015)||GG, GGGG|
|Week of week year (1...53, 01...53)||W, WW||5, 05|
|Parse TimeZone offset (-0700, ...+0700)||ZZ|
To include a literal text in the output format, enclose it in square brackets .
Here's an example of how a pattern like the following might result in a date like the following:
- Pattern → MMM DD YYYY [at] h:mm:ss.SSS A
- Date → Jan 23 2017 at 13:10:15.123 PM.
Let's transform the Eventdate field in the
demo.ecommerce.data table to a more text-friendly format.
Click Create column and select the Format date (formatdate) operation. Assign a name to the new column - let's call it simply FormatDate. Now, add the following arguments:
- Timestamp: Select the Eventdate column.
- Date format: Enter the following pattern: [Event occurred on] MMMM D [of] YYYY [at] HH:mm
Click Create column and you will see the following result:
Let's say we need to adapt the formatted date for French-speaking people. Click the pencil icon on the breadcrumb entry to edit the operation and add the following arguments:
- Time Zone: We can specify the time zone in France (CET or Central European Time) or any other.
- Locale: We need to specify the code for French, which is "fr".
A few adjustments can be made in the Date Format argument if desired. We can put the free text in French and swap the order of the day and month to make it more French-like. Enter the following pattern: [Survenue le] D MMMM YYYY [à] HH:mm
Click Create column when you finish.
How does it work in LINQ?
Use the operator
as... and add the operation syntax to create the new column. These are the valid formats of the operation:
formatdate(timestamp, string, string)
formatdate(timestamp, string, string, string)
Copy the following LINQ scripts and use them to replicate the previous example:
from demo.ecommerce.data select formatdate(eventdate, "[Event occurred on] MMMM D [of] YYYY [at] HH:mm") as FormatDate
from demo.ecommerce.data select formatdate(eventdate, "[Survenue le] D MMMM YYYY [\xe0] HH:mm", "CET", "fr") as FormatDate