topic Re: String to date in QlikView

String to date

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: String to date

swuehl — Thu, 21 Apr 2016 15:04:22 GMT

You can do it like

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

Re: String to date

Fri, 22 Apr 2016 05:02:57 GMT

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

Re: String to date

swuehl — Fri, 22 Apr 2016 09:13:25 GMT

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