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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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

 

Nebula
Nebula

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