community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Knowledge Base

Definitive answers from Designer experts.
New Data Science Blog

Check out the latest post: All Models Are Wrong

READ MORE

Date Conversions

Alteryx Alumni (Retired)

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

Comments
Meteoroid

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! 

Alteryx Alumni (Retired)

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

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?

Community Operations Manager
Community Operations Manager

@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

Thank you, that was helpful!