Alteryx Designer Discussions

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

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!

convert date to number - only the year


Hi all,

I have an issue: I have a  double which corresponds to a year (e.g. 2013), I would like to convert it to a date format but still keeping only the year and without adding month and day.

I tried converting it to string and then using DateTime but it automatically generates 2013-01-01, instead I would still like to have only "2013" to use DateTimeDiff with another date. I tried using DateTimeFormat but its output is a string, so again I cannot use DateTimeDiff with it.
Does anybody have any idea?

Thank you for your help!



Alteryx Certified Partner
Alteryx Certified Partner

Hi @OttaviaBertolli, in all database type systems, and within Alteryx, a field of date type must contain both the year, month and day.


If you wish to use the datetimediff() functionality then convert both your dates into the YYYY-MM-DD format.



17 - Castor
17 - Castor

Hi @OttaviaBertolli


What do want to subtract from your "year" value?  Without the month and day info, the only thing you can reliably subtract is another year.  In this case you just treat the years as numbers and do normal math. If you want to subtract another date, you have to standardize on which date in your year you will be using as the "start date" If you are trying to model a fiscal year that end on March 31, for instance, you can use a formula tool with tostring([YEAR],0) + "-03-31"