String to date

[#1]

Good day,

I'm new to Qlik and need some help.

I do have a field in my QVD called Month_Code and looks like 201603. This code is a string that refers to the year and month. I need to convert this to a date where the first part of the string will be 2016 as the year and 03 as the month. The day can be either the 1st or last day of the month.

What is the best way to convert the string?

[swuehl]

You can do it like

Date(Date#(Month_code, 'YYYYMM')) as YearMonth

[#3]

I tried this one, but it did not apply correctly. the following code however did the trick:

(year(Date(Makedate(left(MONTH_CODE,4), right(MONTH_CODE,2), '01'),'YYYY/MM/DD'))

& month(Date(Makedate(left(MONTH_CODE,4), right(MONTH_CODE,2), '01'),'YYYY/MM/DD')))

[swuehl]

Your code will return something like

2016Mar

right?

Though the format may be like intended, please note that this code will not return a value that can be used in QV date / time functions,nor will it be appear in a proper sort order. It just returns a text value.

Have a look at

Get the Dates Right

Why don’t my dates work?

On Format Codes for Numbers and Dates

You can add a format code to my first expression:

LOAD *,

Date(Date#(MONTH_CODE,'YYYYMM'),'YYYYMMM') as RealNewDate,

  (year(Date(Makedate(left(MONTH_CODE,4), right(MONTH_CODE,2), '01'),'YYYY/MM/DD'))

& month(Date(Makedate(left(MONTH_CODE,4), right(MONTH_CODE,2), '01'),'YYYY/MM/DD'))) as YourNewDate;

LOAD * INLINE [

MONTH_CODE

201603

];

[#5]

Thank you, Appreciated.

[oknotsen]

If your question is now answered, please flag the Correct Answer.

If not, please let us know what part of your topic still needs answering .