Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Should you comparison tests be >= $(STARTDATE) and <= $(ENDDATE) ?
Regards,
Gordon
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);
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
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
Thanks, single quotes around the startdate and enddate did the trick..