topic Re: YYYY format in Date Datatype in Alteryx Designer Desktop Discussions

YYYY format in Date Datatype

Kristin — Thu, 13 Jun 2024 21:53:17 GMT

Hello everyone,

Happy Friday! Been stuck on this one for a bit. None of the posts I've looked through indicate a solution. I'm trying to set a date column to yyyy format. So, I first change the date in the formula tool using DateTimeYear([INCEPTION_DATE]) and it converts it to string. Later in the workflow I use the Datetime tool to convert it back to Datetime from String using the custom format of yyyy. But the output shows yyyy-mm-dd. How can I get it to be a Date datatype and shows yyyy?

Thanks in advance for reading this.

Kristin

Re: YYYY format in Date Datatype

WillBowman — Fri, 01 Mar 2019 16:16:40 GMT

So date datatypes are all YYYY-MM-DD and datetime YYYY-MM-DD hh:mm:ss. If you want a string of the year, DateTimeFormat([date], "%Y") will do it.

Does that answer your question?

EDIT: Or your function DateTimeYear([INCEPTION_DATE]) will get the year too it looks like. Bottom line, date and datetime are structured fields and you cannot change the presentation

Re: YYYY format in Date Datatype

Kristin — Fri, 01 Mar 2019 16:20:43 GMT

Ok so there isn't a way to format yyyy as a date. Got it. Thanks!

Re: YYYY format in Date Datatype

Thableaus — Fri, 01 Mar 2019 16:38:21 GMT

You're right, @Kristin

I'd recommend you to use Year as a Number, so calculations and sorting are easier to do.

Cheers,

Re: YYYY format in Date Datatype

Kristin — Fri, 01 Mar 2019 17:16:38 GMT

There's a governing body that is requesting it to be in this format. I'm not sure this is something I can negotiate. I was hoping that I could set it up to be a date type yyyy-mm-dd and then use the formula tool to Left([DATE FIELD],4) as a hack. But, this doesn't work either.

Re: YYYY format in Date Datatype

danilang — Fri, 01 Mar 2019 17:47:23 GMT

hi @Kristin

Does your governing body have visibility into your workflow, or do they only see the output? If it's the later then use one of the options provide to extract the year just before the output. If its the former, then challenge them to show you any system that can internally represent a date data type without a month or day value.

Dan

Re: YYYY format in Date Datatype

Kristin — Fri, 01 Mar 2019 17:51:17 GMT

They only see the output and I'd have to agree with you. Not even Excel or Oracle accept this format. I appreciate all the feedback. I'm going to close this out.

Re: YYYY format in Date Datatype

Kristin — Fri, 01 Mar 2019 22:31:43 GMT

I got on the phone with an associate from this governing body today, and it turns out they weren't a developer and didn't know that yyyy wasn't a Date datatype. I didn't know either so I can't point fingers...I just always assumed you could change it from string/custom. Lesson learned. Thanks folks!

Kristin