Date Conversions

[RodL]

When I'm out showing my customers some of the capabilities of Alteryx, I find that one question that always seems to get a "yeah!" response is "Any issues with date formatting?". 

Now while this subject has probably been covered in the Alteryx KB elsewhere, I thought I would deal with a specific question that I just got this morning that can be the 'platform' for handling a lot of situations.

The question was "How do I get from a date like this...MM/DD/YYYY...to where I have the day integer, the month name, and the year in three different columns?"

Most Alteryx users know that there is a DateTime tool in the Parse category, but they also have found that all date formats aren't necessarily included there. What I have found that many users don't know is how flexible date formatting is when using a couple of the DateTime functions in the Formula tool. 

These two functions are DateTimeParse and DateTimeFormat. The first one takes a String data type that represents a date and/or time and returns the value in a DateTime data type (you select the data type for it to return). The second one takes a Date, Time, or DateTime data type and returns a string in the format designated. The key to using either of these are the various 'specifiers' you can use within them. (You can find the list of specifiers in the DateTime Operations Help article.)

So for the above question, you are starting out with a string in a given format and basically want to get it into a string with a different format (and then separate it into columns).

The formula expression for this would be...DateTimeFormat(DateTimeParse([Date],'%m/%d/%Y'),'%d|%B|%Y')...where I am actually nesting the one function within the other. The 'inside' function takes the string and converts it to a Date type, and the 'outside' function converts it back to a String. As you can see, it doesn't matter what 'separators' you have for the dates...in this case I used the 'pipe' symbol because I like to use it in the Text to Columns tool (which is the tool I use to separate the date parts into columns), but many date formats use dashes, periods, or even spaces.

I use these two functions in combination like this all the time to get from one date format to another. And if you check out the Help article mentioned above, you will see there are specifiers for Day of the Week (which allows you to create a process that gives you only business days), 24-hour clock, AM/PM indicators, and even time zone.

So having worked with dates and times from a number of different data sources, I know how 'weird' some of them can be. These two Alteryx DateTime functions make bringing in that data and using it in analysis MUCH easier!

I've attached an example that deals with the specific question above. Hope this helps!

Rod Light

Sr. Solutions Engineer

[Henrik]

I try to change a column type to disallow nulls (meaning NOT NULL instead of NULL)

The column I try to change have 100% values so no need to replace or other thing...only making the column to disallow Nulls when imported to MS SQL database.

Help please! 

[RodL]

Henrik,

It is better to post questions such as this to the forum areas rather than to Knowledge Base articles. You have more exposure to help when doing that.

That said, what it sounds like you are asking about is a MS SQL server setting within a table. Those types of settings (e.g. allowing Null values in a field) are not determined within Alteryx and are done through the SQL Server Management Console (or SQL scripts).

Thanks,
Rod

[Costco_Edward]

RodL,

When I click on your link DateTime Operations Help article I receive an error msg statingthis website requires you to login, but I am logged in.  Can you double check that this link still works or provide an updated one?

[DanM]

@Costco_Edward,

That link is referencing an old help URL. You should be able to find what you are looking for here. We will update.

https://help.alteryx.com/current/Reference/Functions.htm#Date/Time

DanM

[Costco_Edward]

Thank you, that was helpful!

[FutureShorter-33]

Thank you for including the example, it was a great supplement to your explanation. 

[AliAS2020]

Thanks a lot, it was helpful with parsing Date/Time field into Month/Year and Time with time zone fields.

All the best.

AliAS

[adrilarotta]

Hello, 

Someone knows which is the best way to transform a string data like this 20200623 into a date 23-06-2020.

Thanks

[AliAS2020]

@adrilarotta 

There is few ways to do that one of them using formula expression and use:

DateTimeFormat(DateTimeParse([Field1],'%Y%m%d'),'%d-%m-%Y')

the DateTimeParse will first parse the incoming string, it have to be string, and tell that first 4 numbers are the year, %Y, the next 2 numbers are the month,%m, and the last 2 are the days,%d.

Then the DateTimeFormat will take that parsing and you can form the format you want, in your case 23-06-2020 => %d-%m-%Y.

Please note that if you want the output be date format, that will return error because the date format for Alteryx need to be 2020-06-23.

Here you can find a list of all DateTime specifiers.