where field between variable and variable

[#1]

I want to load data between two predefined dates in the script.

I have the following code but something does not work properly in the where clausule:

//************************************************
DATE_MAP:
MAPPING
LOAD
date
date(GoodDate, 'DD-MM-YYYY') as Date
FROM ...........
WHERE GoodDate >= $(STARTDATE) and
GoodDate >= $(ENDDATE);

Any suggestions? thanks!

[#2]

Should you comparison tests be >= $(STARTDATE) and <= $(ENDDATE) ?

Regards,

Gordon

[#3]

of course enddate is <= ......little copy paste mistake ;-)) ...

the original looks like that but when I reload zero lines will fetch while I'm sure there are over 3000 records between the start and enddate.

//************************************************
DATE_MAP:
MAPPING
LOAD
date
date(GoodDate, 'DD-MM-YYYY') as Date
FROM ...........
WHERE GoodDate >= $(STARTDATE) and
GoodDate <= $(ENDDATE);

[#4]

What are the values of the variables STARTDATE and ENDDATE? What is the format of GoodDate in the data? It would seem there is a mismatch.

If STARTDATE and ENDDATE are dates in the format DD-MM-YYYY then you need to format them for the where condition:

WHERE date(GoodDate, 'DD-MM-YYYY) >= $(STARTDATE) and

date(GoodDate, 'DD-MM-YYYY) <= $(ENDDATE)

If this resolves the problem you might also want to consider a 'preceeding load' to keep things tidier.

Regards,

Gordon

[stephencredmond · Accepted solution]

Hi,

Depending on the values that you have, you may simply need some single quotes on the dates:

DATE_MAP:
MAPPING
 LOAD
date
date(GoodDate, 'DD-MM-YYYY') as Date
FROM ...........
WHERE GoodDate >= '$(STARTDATE)' and
 GoodDate <= '$(ENDDATE)';

Regards,

Stephen

[#6]

Thanks, single quotes around the startdate and enddate did the trick..