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: 
nareshthavidishetty
Creator III
Creator III

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

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

4 Replies
swuehl
MVP
MVP

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

QlikCommunity_Thread_219715_Pic1.JPG

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
Creator III
Creator III
Author

Hi,

We need convert the below into century format.

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


Thanks....

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein