Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
b_garside
Partner - Specialist
Partner - Specialist

Date Formating conversion from SQL?

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!

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

"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


View solution in original post

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


talk is cheap, supply exceeds demand
b_garside
Partner - Specialist
Partner - Specialist
Author

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


talk is cheap, supply exceeds demand
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

"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
Partner - Specialist
Partner - Specialist
Author

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


talk is cheap, supply exceeds demand
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

"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