convert month Int 16 to String

[ll1100000]

There are three columns in my raw data, how can I change the Month from int 16 to string?  Thank you.

Year	Month	POS
2017	7	100
2017	8	258
2017	1	500
2018	9	658

So it will show like below? 

Year	Month	POS
2017	Jul	100
2017	Aug	258
2017	Jan	500
2018	Sep	658

I guess I need something like "iff[month] = 1, then Jan, else Feb?

Or is there any other way do to this beside using the transpose function?

[NicoleJohnson · Accepted solution]

You could use this formula in the Formula tool:

DateTimeFormat(DateTimeParse(ToString([Month]),"%m"),"%b")

Converts the field to a string... then turns it into a standard DateTime value with DateTimeParse formula ("%m" being the format for month as a number)... then formats it as a 3-letter month name using DateTimeFormat ("%b" being the format for 3-letter Month name).

FYI - you'll likely want to create a new field in the formula tool rather than reusing the existing Month field if it's set as type Int16, as the format using words will result in a Null value if kept in the same field.

Does that help? :)

NJ

[NicoleJohnson]

... and in case the DateTime formatting is as much of a mystery to you as it was (is) to me, this post has some good info/a link to the formats used by both DateTimeParse and DateTimeFormat. :)

[ll1100000]

Hi Nicole;

Thank you very much for your solution.  This formula works!

But for my fault, I just realized that for financial purpose, our financial calendar is different from the regular calendar because the first month is Feb

Therefore, 1 = Feb, 2=Mar, 3 = April...

so, in this case.... I don't know how to adjust this formula

[NicoleJohnson]

Couple options... you could do something like below, which will add a month to the original number (or add 11 in the case of January):

DateTimeFormat(DateTimeParse(ToString(IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF),"%m"),"%b")

Or you could opt for a SWITCH formula instead:

DateTimeFormat(DateTimeParse(ToString(Switch([Month],[Month]+1,12,1)),"%m"),"%b")

The switch formula lets you pick the field, set a default response, and then pick the result you want if the following values are found. So in this case it will add a day for everything except when the original month is 12, when it will replace it with 1.

Does that help? :)

NJ

[ll1100000]

Hi Nicole;

I had error message for the 1st formula

DateTimeFormat(DateTimeParse(ToString(IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF),"%m"),"%b")

 "Formula invalid type in subtraction operator"

For the 2nd formula, I also had error message as well  

DateTimeFormat(DateTimeParse(ToString(Switch([Month],[Month]+1,12,1)),"%m"),"%b")

"type mismatch in operator +"

[NicoleJohnson]

If your month column is already type format of string, you'll need to convert it to a number first before adding or subtracting anything to it (wrap ToNumber() around your month field). Which means you'll be converting it to a number to do the math, then back to a string to do the date formatting functions...

If you get too many nested formulas that it becomes messy or hard to follow, you could also just set up a simple text input that has the month numbers in one column, the 3-letter words you want them to be for each month number in the second column, and then use a Join (or a Find and Replace) tool to get the correct month names in there by matching the month number in your original data to the month number column in the text input. Does that make sense?

There are probably 20 different ways to skin this cat ;)

NJ

[ll1100000]

I have changed the data type to "int 16" by using select function, then changed the 1st formula as below....

But I think I still had some syntax errors....

DateTimeFormat(DateTimeParse(Tonumber(Month)(IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF),"%m"),"%b")

[ll1100000]

Finally~~~I got it ~~I break down the formula into two parts~~~Thank you very much for help SQL idoit like me :)

IF [Month] = 12 THEN [Month] - 11 ELSE [Month] + 1 ENDIF

DateTimeFormat(DateTimeParse(ToString([Month]),"%m"),"%b")