Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Hi,
Can you try this?
left(' MM/YYYY',index( ' MM/YYYY', '/' )) as MONTH
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 ':'
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
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.
Not a problem. I am glad I was able to help.
Best,
Sunny