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

[nareshthavidishetty]

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...

[swuehl]

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.

[MarcoWedel]

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

[nareshthavidishetty]

Hi,

We need convert the below into century format.

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

Thanks....

[jonathandienst]

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