Using today() function

[qw_johan]

Hi

I am joining two tables using "left join load…" which works good.
I then want to filter out certain rows that are old using a "where" statement but
I keep getting different results using the following code:

where date_t >= Date#(Today(), 'yyyymmdd') OR date_t = ''

and

where date_t >= '20110208' OR date_t =''

…why is this? I don't understand.
Shouldn't I get the same results? Doesn't today() actually mean today?
Using today() I also get rows with dates from January (30^th, 23^rd, 9^th…)..I only expect to get rows from Feb 8^th and later (which I do get if I enter the real date).

My tables look like this:

EMP:

LOAD

@2 as id_nr,

@4 as date_f,

@5 as date_t

..

FROM

emp.txt(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 8 lines)where @5>='20110208' OR @5='';

;

//where @5 >= Date#(Today(1), 'yyyymmdd') OR @5 = '';

//where @5>='20110208' OR @5='';

LEFT JOIN LOAD

@2 as id_nr,

@3 as last_name,

@4 as first_name

..

FROM

pers.txt(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 8 lines);

I really want to avoid having to "hard code" a date in my script.

Any help appreciated.[:D]

[Miguel_Angel_Baeyens]

Hello Johan,

That WHERE statement is correct and it should work if the function Date() is evaluated properly. The following code works:

RawData:LOAD Date(Date('01/01/2010') + Rand() * 730, 'YYYYMMDD') AS DateAUTOGENERATE 2000; // Just dummy data to compare with ValidData:LOAD *, RecNo() AS Dummy // Another field to not concatenate with previous tableRESIDENT RawDataWHERE Date < Date(Today(), 'YYYYMMDD'); // Here the date is being evaluated correctly DROP TABLE RawData;

In the example above, "Date" field as an actual date field (so numeric) and that allows not to quote the function. So to properly evaluate the date when it's a string and passed as valid value for a field, it would be

LET vToday = Date(Today(), 'YYYYMMDD'); RawData:LOAD Text(Date(Date('01/01/2010') + Rand() * 730, 'YYYYMMDD')) AS DateAUTOGENERATE 2000; ValidData:LOAD *, RecNo() AS DummyRESIDENT RawDataWHERE Date < '$(vToday)'; DROP TABLE RawData;

Hope that helps