topic Re: MM/DD/YYYY date format to 'CYYMMDD' Conversion in QlikView

MM/DD/YYYY date format to 'CYYMMDD' Conversion

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am having an date variable in the format 'MM/DD/YYYY' and we need to convert this into century format 'CYYMMDD'.

May we know how to convert the date formats.Below was the variable logic used.

let vYesterday = (Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100 + day(Today()-1); = 1160605

let vesterdaynormal_date =date(Date#(Right(vYesterday,6),'YYMMDD'),'MM/DD/YYYY');= 06/05/2016

LET vMonthstart =MonthStart( AddMonths((Today()-1),0)); = 06/01/2016

so here we need to convert vMonthstart into century format.

So that we can use these variables in the below logic.

LOAD *;

SELECT DISTINCT A7AACD , count(,A7BANB) as eSign_initiated_MTD

FROM DSA7CPP

WHERE A7AIDT >= $(vMonthstart) AND <= $(vDDD) AND A7BANB = IN (SELECT BPBANB from INBPCPP)

GROUP BY A7AACD;

May i know the above logic will work in qlikview or else if any modifications required.

Thanks...

Re: MM/DD/YYYY date format to 'CYYMMDD' Conversion

swuehl — Mon, 06 Jun 2016 22:09:53 GMT

Regarding your monthstart definition, I think this should return the correct format:

LET vMonthstart =(Year((TODAY()-1))-1900)*10000 + Month((TODAY()-1))*100 + 1;

Regarding you SELECT statement, everything between SELECT and the semicolon will be sent to the SQL driver and executed by this driver. You need to write SQL code that works for your driver, QV will not interprete this code.

Re: MM/DD/YYYY date format to 'CYYMMDD' Conversion

MarcoWedel — Mon, 06 Jun 2016 23:56:57 GMT

Hi,

maybe this helps

SET vDateCentForm = Dual((Year($1)-1900)&Date($1,'MMDD'),$1);

table1:
LOAD *,
     $(vDateCentForm(Date)) as DateCentForm;
LOAD DayName(Rand()*100000) as Date
AutoGenerate 20;

regards

Marco

Re: MM/DD/YYYY date format to 'CYYMMDD' Conversion

nareshthavidishetty — Tue, 07 Jun 2016 05:08:49 GMT

Hi,

We need convert the below into century format.

LET vMonthstart =MonthStart( AddMonths((Today()-1),0)); = 06/01/2016 INTO 'CYYMMDD'.

Thanks....

Re: MM/DD/YYYY date format to 'CYYMMDD' Conversion

jonathandienst — Tue, 07 Jun 2016 06:20:26 GMT

Did you try swuehl's suggestion? It looks correct to me.