Issue with date comparisons in load script

[robertmlawton]

I'm really struggling with date comparisons in a loading scripts.

I am loading a text file which has a date  field in YYYY-MM-DD HH:MM:SS format, on loading i convert it to a date field and change the layout to DD/MM/YYYY. I then create a date variable to use for comparison.

a simplified version of my code which demonstrates my issue:

Import.txt file:

ID|Date
1|1992-01-19 00:00:00
2|1996-01-19 00:00:00
3|1998-01-19 00:00:00
4|1996-03-19 00:00:00
5|1999-07-19 00:00:00

INPUT:
LOAD
ID,
Date(Date#("Date", 'YYYY-MM-DD'), 'DD/MM/YYYY') as Date
FROM [lib://Import.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq);


Let vInitalReportDate = Date(Date#('31/08/2014', 'DD/MM/YYYY'), 'DD/MM/YYYY');


OUTPUT:
NoConcatenate
LOAD *
Resident INPUT
WHERE Date <= $(vInitalReportDate);

Every date in the file is less that the comparison date variable but no rows are loaded into OUTPUT. What am i doing wrong, it seems such a simple thing?

[Claudiu_Anghelescu]

INPUT:
LOAD
ID,
Date("Date",'YYYYMMDD') as Date
FROM [lib://Import.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq);

Let vInitalReportDate = Date(Date#('31/08/2014', 'DD/MM/YYYY'), 'YYYYMMDD');

OUTPUT:
NoConcatenate
LOAD *
Resident INPUT
WHERE Date <= $(vInitalReportDate);

DROP TABLE INPUT;

[marcus_malinow]

Try changing your WHERE clause by enclosing $(vInitalReportDate) in single quotes

WHERE Date <= '$(vInitalReportDate)';

[tresesco]

Try like:

INPUT:
LOAD
ID,
Date(Date#("Date", 'YYYY-MM-DD hh:mm:ss'), 'DD/MM/YYYY') as Date
FROM [lib://Import.txt]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq)
Where Date#("Date", 'YYYY-MM-DD hh:mm:ss') <= MakeDate(2014, 8,31);