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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Convert String to Date but not DateTime

Atom

Hi,

 

I have the following string I need to convert to a date:

 

2017 / 7

 

When I use the DateTime Parse tool it gives me the right date but not in the format I'm looking for. I want dd-mm-yyy not yyy-mm-dd. The reason for this is when I publish the workflow directly to Power BI it adds in the Time which I don't want.

 

Can someone help me convert 2017 / 7 to 01-07-2017?

 

Thanks,

mholland

Alteryx Certified Partner
Alteryx Certified Partner
Actually, you want: 2017-07-01 (I think)

2017 / 7

DateTimeFormat(DateTimeParse([date]+"-01","%Y / %m-%d"),"%d-%m-%Y")



That should work, but it's a string and not a date.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Atom

Hi @MarqueeCrew,

 

That's not quite the date format I'm looking for. I want the day at the start and year at the end - DD-MM-YYYY - not the other way around.

 

Any thoughts on what to try next?

Bolide

Change the specifiers for the DateTimeFormat function

 

See this page: https://help.alteryx.com/current/Reference/Functions.htm?Highlight=datetimeformat

 

Aurora

hi @mholland

 

In Alteryx all dates are represented as 10 character strings always in the following format yyyy-mm-dd.  If you change the order within the workflow, it's no longer seen as a date, but as a string, so you can't perform the normal date operations on it. 

 

If you analyse @MarqueeCrew's code you'll see that 

 

DateTimeFormat(DateTimeParse([date]+"-01","%Y / %m-%d"),"%d-%m-%Y")

the part in red takes your input, "2017 / 7" and converts it to a date.  The outer function in Blue takes this date and returns a string in the dd-mm-yyyy format, "01-07-2017" which is what you need for your output. 

 

If you need to perform date operations before your output, then break this up into 2 controls. First a DateTimeParse tool to convert it to an Alteryx format.  Then perform your operations on the new date.  Just before output add a Formula tool that creates a new field with the formula

 

DateTimeFormat([DateField],"%d-%m-%Y")

 

Output the new field, which will be in dd-mm-yyyy to PowerBI 

 

Dan

 

Atom

Hi,

 

I think the problem may be with Power BI now. It's automatically adding a time of "12:00:00 AM" to the end of my field. I'll try the Power BI Community for an answer.


Thanks for your help.

Labels