script loads data up to a certain date each month

[ioannagr]

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;

[ioannagr]

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?

[ChristianRiegel]

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

[ioannagr]

@ChristianRiegel it's not a string though and i tried yours too but still loads all dates. Thank you though 🙂