Date Formating conversion from SQL?

[b_garside]

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!

[Gysbert_Wassenaar]

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')

[b_garside]

Didnt work. not sure why I just pasted it directly in.

Date(Date#('01-'&Month,'DD-MMM-YY'),'MMM-YY')

[Gysbert_Wassenaar]

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

[rwunderlich · Accepted solution]

"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

[b_garside]

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!

[Gysbert_Wassenaar]

My education continues

[rwunderlich]

"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