topic Re: Date Formating conversion from SQL? in QlikView

Date Formating conversion from SQL?

b_garside — Thu, 20 Dec 2012 15:54:18 GMT

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 — Thu, 20 Dec 2012 15:58:27 GMT

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 — Thu, 20 Dec 2012 16:02:51 GMT

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

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

Gysbert_Wassenaar — Thu, 20 Dec 2012 16:10:12 GMT

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 — Thu, 20 Dec 2012 17:15:17 GMT

"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 — Thu, 20 Dec 2012 17:22:44 GMT

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 — Thu, 20 Dec 2012 17:24:46 GMT

My education continues

rwunderlich — Thu, 20 Dec 2012 18:13:56 GMT

"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