Alteryx datetime tool

Alteryx datetime tool DEFAULT

DateTime Tool

Specify Your DateTime Language

Date/Time Format to String

When you convert date-time data to string data, use the Specify your DateTime Language dropdown to choose the language for your new string column output. Depending on the language, different format options are available.

String to Date/Time Format

When you convert string data to date-time data, use the Specify your DateTime Language dropdown to identify the language of the incoming string data. Depending on the language, different format options are available.

English
  • yyyy-MM-dd hh:mm:ss
  • MM/dd/yyyy hh:mm:ss
  • MM/dd/yy hh:mm:ss
  • dd/MM/yyyy hh:mm:ss
  • dd/MM/yy hh:mm:ss
  • day, dd Month, yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd-MM.-yy
  • dd Month, yyyy
  • dd/MM/yy
  • dd/MM/yyyy
  • dy., Month dd, yyyy
  • MM-dd-yy
  • MM-dd-yyyy
  • MM/dd/yy
  • MM/dd/yyyy
  • Mon dd
  • Month dd, yyyy
  • Month, yyyy
  • yyyy-MM-dd
  • yyyyMMdd
  • HH:mm:ss
  • Custom
简体中文 (Simplified Chinese)
  • yyyy'年'M'月'd'日' ahh'时'mm'分'ss'秒'
  • yyyy-MM-dd hh:mm:ss
  • yyyy'年'M'月'd'日'
  • yyyy'年'M'月'd'日'day
  • yyyy-MM-dd
  • yyyyMMdd
  • HH:mm:ss
  • Custom
Français (French)
  • d Mon yy HH:mm:ss
  • yyyy-MM-dd hh:mm:ss
  • dd/MM/yyyy hh:mm:ss
  • dd/MM/yy hh:mm:ss
  • d Mon yy
  • d Month yyyy
  • day d Month yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd/MM/yy
  • dd/MM/yyyy
  • yyyy-MM-dd
  • yyyyMMdd
  • HH:mm:ss
  • Custom
Deutsch (German)
  • dd.MM.yy HH:mm
  • dd.MM.yyyy HH:mm:ss
  • yyyy-MM-dd hh:mm:ss
  • dd/MM/yyyy hh:mm:ss
  • dd/MM/yy hh:mm:ss
  • dd.MM.yy
  • dd.MM.yyyy
  • d. Month yyyy
  • Day, dd. Month yyyy
  • Day, d. Month yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd/MM/yy
  • dd/MM/yyyy
  • yyyy-MM-dd
  • yyyyMMdd
  • HH:mm:ss
  • Custom
Italiano (Italian)
  • dd/Mon/yy HH:mm:ss
  • yyyy-MM-dd hh:mm:ss
  • dd/MM/yy hh:mm:ss
  • dd/MM/yyyy hh:mm:ss
  • dd/Mon/yy
  • dd Month yyyy
  • Day d Month yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd/MM/yy
  • dd/MM/yyyy
  • yyyy-MM-dd
  • yyyyMMdd
  • HH:mm:ss
  • Custom
日本語 (Japanese)
  • yyyy-MM-dd hh:mm:ss
  • yyyy'年'M'月'd'日'day
  • yyyy'年'M'月'd'日'
  • M'月'd'日'
  • yyyy-MM-dd
  • yyyyMMdd
  • 'hh'時'mm'分'ss'秒' 
  • HH:mm:ss
  • Custom
Português (Portuguese)
  • yyyyMMdd H.mm
  • yyyy-MM-dd hh:mm:ss
  • dd/MM/yyyy hh:mm:ss
  • dd/MM/yy hh:mm:ss
  • d' de 'Month' de 'yyyy
  • Day, d' de 'Month' de 'yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd/MM/yy
  • dd/MM/yyyy
  • yyyy-MM-dd
  • yyyyMMdd
  • HH:mm:ss
  • Custom
Español (Spanish)
  • yyyyMMdd H.mm
  • yyyy-MM-dd hh:mm:ss
  • dd/MM/yyyy hh:mm:ss
  • dd/MM/yy hh:mm:ss
  • d' de 'Month' de 'yyyy
  • Day d' de 'Month' de 'yyyy
  • dd-MM-yy
  • dd-MM-yyyy
  • dd/MM/yy
  • dd/MM/yyyy
  • yyyy-MM-dd
  • yyyyMMdd
  • HH:mm:ss
  • Custom
Sours: https://help.alteryx.com/20213/designer/datetime-tool

The Alteryx-recognized date format is YYYY-MM-DD. After you convert your values to dates to have the Month converted to a numeric version and have the date added, you can simply use a replace statement to replace the "-" with a ".". 

 

Alternatively, you can use a formula to do the date conversion and the replace statement all in one.

 

You will need to create a new column with either of these, due to the size of your Date column. It is a string value limited to only 9 characters, but YYYY.MM.DD is 10 characters. You'll either need to create a new column, or use a Select to change the datatype. 

 

Keep in mind that having your data formatted this way makes the value a string, or text. It is no longer a date and date functions (like datetimeadd, datetimediff, etc.) won't be applicable. I only recommend doing this at the very end of your analysis, for presentation purposes as needed.

 

echuong1_0-1612845350270.png

 

Sours: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/DateTime-tool-issue/td-p/714787
  1. Amazon office chairs
  2. Bmw silver colors
  3. Autozone 10 mile

How to turn a string into a date in just two steps

How many times have you turned a a sting into a date in Alteryx? Learn a new trick to deal with custom date formats more efficiently in this blog!

Let’s say that you want to turn a string field into a date or datetime field. The field looks like this: 

Turn string into a date step one

How do we turn it into a datetime field?

The most natural thing to do might be to use the DateTime tool. Unfortunately, our fields’s format does not correspond to any available datetime format available in the tool. Therefore, we will need to select “Custom” under “Select the format that matches the incoming string field” and type: yyyy-mm-ddThh:mm:ss

Step two, the datetime field

How to do deal with custom date formats in two steps:

However, if you are like me, you won’t enjoy having to type the exact date format. Luckily, there’s a short and sweet alternative solution! Here it is:

STEP 1

Use Formula Tool and type: Replace([date_time], ‘T’, ‘ ‘)

STEP 2

Drag in a Select Tool and change the field’s data type to datetime.

…and you’re good to go! You know how to turn a string into a date!

For a practical example of this trick, check out DavidP’s workflow here.


Wanna learn more about Tableau or Alteryx? Do you want to use these tools to their full potential? Check out our trainings and consultancy services!

Category iconAll

Sours: https://www.theinformationlab.nl/en/2020/09/28/how-to-turn-a-string-into-a-date-in-just-two-steps/
Alteryx - DateTime Example (Challenge #10 Solution)

DateTime Functions

Abbreviated Weekday Name ("Mon")Any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"). Returns an error only if the given text is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date.Full Weekday Name ("Monday")Day name or any valid abbreviation of a day of the week ("mon", "Tues.", "Thur"). Returns an error only if the given text is not a day of the week. Note that Alteryx does not check that the specified day name is valid for a particular date.Abbreviated Month Name ("Sep")Any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not a name of a month.Full Month Name ("September")Month name or any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not a name of a month.The date and time for the computer’s locale.Not SupportedThe Century Number ("20")Not SupportedDay of the Month ("01")1 or 2 digits, ignoring spaces ("1" or "01").Equivalent to %m/%d/%yNot SupportedDay of the month, leading 0 replaced by a space (" 1").1 or 2 digits, ignoring spaces ("1" or "01").Same as %b ("Sep")Any valid abbreviation of a month name ("Sep", "SEPT."). Returns an error only if the given text is not a name of a month.Hour in 24 hour clock, 00 to 23.Up to 2 digits for hour, 0 to 23. Not compatible with %p or %P.
(capital "eye")Hour in 12 hour clock, 01 to 12.Up to 2 digits for hour, 1 to 12. Must follow with %p or %P.The day of the year, from 001 to 365 (or 366 in leap years)3-digit day of the year, from 001 to 365 (or 366 in leap years)24 hours, leading zero is space, " 0" to "23".Up to 2 digits for hour.
(lowercase "ell")12 hours, leading zero is space, " 1" to "12".Not SupportedMinutes, 00 to 59Up to 2 digits for minutes.Month number, 01 to 12.1 or 2-digit month number, 1 or 01 to 12."AM" or "PM"Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format)."am" or "pm"Case blind ("aM" or "Pm"). Must follow %I (capital "eye", hour in 12-hour format).Seconds, 00 to 59Up to 2 digits for seconds.Time in 24-hour notation. Equivalent to %H:%M:%SNot SupportedDay of week as a decimal, 1 to 7, with Monday as 1.Not SupportedThis returns the week number, as 00 – 53, with the beginning of weeks as Sunday.Not SupportedDay of week as a number, 0 to 6, with Sunday as 0.Not SupportedThis returns the week number, as 00 – 53, with the beginning of weeks as Monday.Not SupportedThe date for the computer’s locale.Not SupportedThe 12-hour clock time, including AM or PM (“11:51:02 AM”).Hours:Minutes:Seconds [AM / PM]Last two digits of the year ("16").Up to 4 digits are read, stopping at a separator or the end of the string, and mapped to a range of the current year minus 66 to current year plus 33. For example, in 2016, that's 1950 to 2049.

Limitation with 6-Digit Dates
Because up to 4 digits are read for the year, formats that are intended to have only 2 digits without a separator, such as a 6-digit date stamp (for example, %y%m%d for data resembling 170522 for May 22, 2017), are still read as 4 digits. To work around this limitation, you can...

  • Use 4 digits for the year (for example, 2017 instead of 17), depending on your range of dates.
  • Use the RegEx tool to insert a space after the first 2 digits in the string.
All four digits of the year ("2016").2 or 4 digits are read. 2 digits are mapped to a range of the current year minus 66 to the current year plus 33. For example, in 2016, that's 1950 to 2049.Offset from UTC time ("-600").Not SupportedFull timezone name ("Mountain Daylight Time").Not Supported
Sours: https://help.alteryx.com/20213/designer/datetime-functions

Tool alteryx datetime

DateTime.png

This article is part of the Tool Mastery Series, a compilation of Knowledge Base contributions to introduce diverse working examples for Designer Tools. Here we’ll delve into uses of the DateTime Toolon our way to mastering the Alteryx Designer:

 

Date/Time data can appear in your data in string formats (text fields) or date formats.The DateTime Tool standardizes and formats suchdata so that it can be used in expressions and functions from the Formula or FilterTools (e.g. calculating the number of days that have elapsed since a start date). It can also be used to convert dates in datetime format to strings to use for reporting purposes.

 

The functionality of the DateTime tool is very similar to the DateTime Functions in the Formula Tool, but a little more user-friendly.

 

To change a date datatype to a formatted string field, all you do is select the date field to be formatted, what you would like the formatted date to look like and the name of the new field Alteryx will create:

 

2019-03-01_9-53-56.png

 

 

Converting a date contained in a text field to a proper date format works similarly: you select the field to be formatted, the string that matches the current format and what the output field should be called.

 

2019-03-01_10-03-49.png

 

A date stored as date format will always appear as yyyy-mm-dd when viewed in a Browse Tool .

 

The dy., Month dd, yyyy and day, dd Month, yyyy options allow you to output the day of the week. This is especially useful if you want to differentiate between weekends and weekdays, e.g. if you want to remove business days from your data (if you only have the start and end dates and want to remove business days, refer to this article).

 

If the DateTime tool doesn't cover your needs, go over to this article and this post to explore the options using Date/Time functions in the Formula Tool.

 

By now, you should have expert-level proficiency with the DateTime Tool! If you can think of a use case we left out, feel free to use the comments section below! Consider yourself a Tool Master already? Let us know at c[email protected] if you’d like your creative tool uses to be featured in the Tool Mastery Series.

Additional Information
Click on the corresponding language link below to access this article in another language -
Portuguese
Spanish
French
German
Japanese

Sours: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-DateTime-Tool/ta-p/38302
Alteryx Weekly Challenge 206 - Date Time Parse Practice

.

You will also like:

.



895 896 897 898 899