Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, can anybody tell me why this script loads only data from 19th of each month to end of month?
For example, it loads from 19/2/2021 to 28/2/2021, from 19/3/2021 to 31/3/2021, from 19/4/2021 to 31/4/2021 and so on.
let v_CreateTime = date(QvdCreateTime('$(vPath)MyQVD.qvd'),'DD/MM/YYYY');
Trace $(v_CreateTime); // (it's 19/2/2021)
NoConcatenate
TempDate:
load Distinct date(floor(mydate),'DD/MM/YYYY') as Date
where date(floor(mydate),'DD/MM/YYYY')>'$(v_CreateTime)';
SQL SELECT mydate
FROM Table1
where date_format(mydate,"%d/%m/%Y")>'$(v_CreateTime)';
LET NumRows=NoOfRows('TempDate');
trace $(NumRows);
For n=1 To $(NumRows)
let vDate = Peek('Date',$(n)-1,'TempDate');
trace $(vDate);
TempSize:
NoConcatenate
LOAD
id,
size;
SQL select
tab1.`id`,tab1.`mydate`,tab2.`id_fk`
tab2.`size`
Table1 tab1
inner join
Table2 tab2
on tab1.`id`=tab2.`id_fk`
where date_format(tab1.`mydate`,"%d/%m/%Y")='$(vDate)';
TotalSize:
LOAD
id,
sum(size) as "TotalSize"
resident TempSize
group by id;
DROP TABLE TempSize;
next n;
drop table TempDate;
mydate is a timestamp in the db. I searched and found that to be able to compare with format like 'DD/MM/YYYY' i have to use date_format(datefield,"%d/%m/%Y") in MySQL. I already declared v_CreateTime as 'DD/MM/YYYY' , even floored it!
I see your point @ChristianRiegel , although i don't see how they can't compare in this "where" part.
Why do you suggest to use "string" function?
I'm only have experience on Oracle and MSSQL, but there i have to convert the input to the intern Format.
like this SQL :
SQL SELECT mydate
FROM Table1
where mydate > STR_TO_DATE('$(v_CreateTime)','%d/%m/%Y');
@ChristianRiegel it's not a string though and i tried yours too but still loads all dates. Thank you though 🙂