Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I was hoping to convert a date from SQL in 2012-12 'YYYY-MM' to 'MMM-YY' Dec-12 ?
I'm using this as a calculated Dimension.
I tried this but no luck. Date(Date#(Month,'MMM-YY'),'MMM-YY')
Thanks!
"Your Month isn't a date since it's lacking days".
I disagree. The QV date#() is very good about providing defaults for missing date components.
date#('2012-04', 'YYYY-MM') = 2012-04-01
date#('2012', 'YYYY') = 2012-01-01
There should be no need to supply the day value. The error in the original post
Date(Date#(Month,'MMM-YY'),'MMM-YY')
is the date# format. It should be YYYY-MM to match the input value.
Date(Date#(Month,'YYYY-MM'),'MMM-YY')
-Rob
Your Month isn't a date since it's lacking days. You need to make a date from it first: Date(Date#(Month&'-01','YYYY-MM-DD'),'MMM-YY')
Didnt work. not sure why I just pasted it directly in.
Date(Date#('01-'&Month,'DD-MMM-YY'),'MMM-YY')
2012-12 is format YYYY-MM. If you add days you get 2012-12-01 which is YYYY-MM-DD which is not DD-MMM-YY.
So try using the YYYY-MM-DD format string: Date(Date#(Month&'-01','YYYY-MM-DD'),'MMM-YY')
edit: fixed hyphen typo
"Your Month isn't a date since it's lacking days".
I disagree. The QV date#() is very good about providing defaults for missing date components.
date#('2012-04', 'YYYY-MM') = 2012-04-01
date#('2012', 'YYYY') = 2012-01-01
There should be no need to supply the day value. The error in the original post
Date(Date#(Month,'MMM-YY'),'MMM-YY')
is the date# format. It should be YYYY-MM to match the input value.
Date(Date#(Month,'YYYY-MM'),'MMM-YY')
-Rob
Excellent update. Ok, so my input mask was wrong now I see.
I was using the Date# function incorrectly which is more powerful than I realized.
I will be bookmarking this post!
Thanks Rob!
My education continues
"My education continues"
And so do your contributions. You are on some kind of record pace. Thanks for all your answers to the Community.
-Rob