How to separate a fields

[#1]

Hi,

I have some dates coming in in MM/YYYY format and I want to separate those out as Months, and Years.  For some reasons I won't get into I cannot use Month(Date) as Month and Year(Date) as Year since the time formats are a little out of whack at the moment.

The other issue I have is that some for instance January is not 01 rather 1 so I cannot use a number to count the separation but would rather use the '/'. 

So how can I take everything to the left of the / and call it month and everything to the right of the / and call that year?  The field is called Date.

[datanibbler]

Hi Jason,

maybe your date comes as a STRING, not in proper date_format? In that case, try SUBFIELD - you can use the '/' as separator to split out the parts.

HTH

[#3]

Hi,

Can you try this?

left(' MM/YYYY',index( ' MM/YYYY', '/' )) as MONTH

[buzzy996]

try this,

subfield(S, ';' ,2) returns 'cde' if S is 'abc;cde;efg'

subfield(S, ':' ,1) returns NULL if S is an empty string

subfield(S, ':' ,1) returns an empty string if S is ':'

[sunny_talwar · Accepted solution]

You can try this:

Month(Date#(Date, 'MM/YYYY')) as Month

Year(Date#(Date, 'MM/YYYY')) as Year

MonthName(Date#(Date, 'MM/YYYY')) as MonthYear

HTH

Best,

Sunny

[#6]

Thank you!!  That is exactly what I was trying to do but I gave up.  I wrapped the Date# function too early, I had it before Month and Year which is why it wasn't working before.  Thanks again.

[sunny_talwar]

Not a problem. I am glad I was able to help.

Best,

Sunny