yyyymmdd to dd-month-yyyy conversion

[keerthanak222]

I have my source date data in the string format "20130101" i wanted to convert it to 01.Jan.2013 format when i use the formula DateTimeParse([_CurrentField_],"%d-%b-%Y") in Multi Field formula after changing data type to date it is showing me the below error

ConvError: Multi-Field Formula (17): DATETIMEPARSE: Error in DateTimeParse: Month number is out of range 1..12

How to achieve this conversion

[s_pichaipillai · Accepted solution]

@keerthanak222

try this . for formating the date, please use datetimeformat function :)

DateTimeFormat(LEFT(tostring([DateNumber]),4)+'-'+SUBSTRING(tostring([DateNumber]),4,2)+'-'+SUBSTRING(tostring([DateNumber]),4,2),'%d.%b.%Y') 

Note: you don't need ti use Multirow formula tool for this, if it is a single filed

Just use the single Formula tool :)

[s_pichaipillai]

Attaching the sample workflow 

[keerthanak222]

Thank u so much for the help...Nice guidence for beginers like me.. tnx again

[s_pichaipillai]

you welcome and welcome to the Great community 

also take a look at here for date time funtions 

http://help.alteryx.com/10.0/index.htm#Reference/DateTimeFunctions.htm

[jdunkerley79]

I would do it a very similar way (though might use DateTimeParse to read in) but wanted to also mention the DateTime tool. It allows string to date conversion and vice versa. Main problem is an annoyingly limited list of format (please fix in v11!!!).

[keerthanak222]

so is there  a way to get this in DATE data type.

currently using this function DateTimeFormat(LEFT(tostring([DateNumber]),4)+'-'+SUBSTRING(tostring([DateNumber]),4,2)+'-'+SUBSTRING(tostring([DateNumber]),6,2),'%d.%b.%Y')  im able to get it as string . 

[jdunkerley79]

If you want to read in the Date as a date just specify the type as date in the formula tool:

and use something like:

DateTimeParse([Field1],'%Y-%m-%d')

[hedouard]

I have a similar issue and have tried to convert a date column and getting errors.  The format would be as below

[nick_schimweg]

Hi hedouard,

You weren't very descriptive with the issue you're having. Please look at the attached workflow and let me know if it helps and makes sense. I didn't address the column above labeled 'Convert to New date' because it seem to disagree with the other columns. Also, please create a new post next time as this one was already marked 'Solved' it makes for a confusing read.

-Nick