HELP! Datetime versus Date

[JET]

Hello,

I have what I think is a very simple problem, and I've spent almost two hours scouring the posts for an answer, but I can't seem to find what I'm looking for. I have two tables that I want to join, and both are coming from excel (.xlsx) files. Each table contains a date field in the excel standard "short date" format.

One table has hh mm ss information that AlteryX recognizes as a Datetime data type, and the other table does not contain hh mm ss information. 

I understand that AlteryX can only "read" dates in the yyyy-mm-dd format. I have not problem getting the date field from both tables into that format so that Alteryx understands this is a date and not a string.

What I CANNOT seem to do is make AlteryX ignore the hh mm ss information. I have tried many different tools and formulas, but even after trimming the data, it comes back after using the parsing tool! 

I feel foolish for even asking, but the majority of the questions and responses seem centered around either converting to a date in the first place or getting a better-looking format in the output. My problem is that I cannot seem to get AlteryX to take records in a Datetime data type and compare them to records in a Date data type.

Am I missing something totally obvious here?

[T_Willins]

Hi @JET,

When you are trimming the data are you converting the field type to a string type?  It might be easier to join dates in string format than in datetime format.

[RolandSchubert · Accepted solution]

Hi @JET ,

if you want to convert the data you imported from Excel from DateTime data type (which is assigned to the field based on the data) to a Date data type (assigned to all other data) you can use a Multi-Field Formula tool (even oif you only want the change for one field).

Select the field you want to change, check "Change output type to" and select "Date". The formula should be [_CurrentField_].

The result will be a Date field (as created for all other files you imported).

Does this help?

Best,

Roland

[danilang]

Hi @JET 

Some more options

- Use a select tool(or any tool where the select functionality is available) to change the type to Date.  This will trim the Time information

- Use ToDate([DateTimeField]) in a formula tool set to create a new Date field  

Dan

[danespoors]

If you input the date as a string, there is a tool under "Parse" called "DateTime" which allows you to turn a string field into a Date/Time Format. You can match the format of the string input so even if you have 'DD-MM-YY', then you can select that option in the tool and it will convert it to the alteryx format 'yyyy-mm-dd hh:mm:ss'.

This tool is quite handy and as long as the column you're importing is all of the same format, then it will convert it to the appropriate datetime you need.

[JET]

@RolandSchubert

That was it exactly! I'm new to AlteryX and haven't learned what all the tools do. I find myself struggling to get through a lot of data conversion errors, so hopefully this tool will help reduce the work-arounds for all of them!

Thanks for your help