ERROR: CLI cursor fetch error: [Oracle][ODBC]Invalid datetime format

[Lost_Gary]

This is the error that I am continually receiving as I attempt to pull data from a very large data set (ERROR: CLI cursor fetch error: [Oracle][ODBC]Invalid datetime format).  

I am not sure, there maybe an error in the data - but the dataset is too large for me to investigate, but there is also the chance that it may be the date/time format that I am unable to query.  I have tried several formats including the following codes:

data wish;

set huge_data;

where prod_date = mdy(01,01,2020);

where prod_date = dhms(01JAN2020:00:00:00);

run;

I have also tried a number of other things (pulling via proc sql, pulling based on > a date), but the process continues to bomb at some point (usually after a couple of hundred thousand records depending on which day I select - this makes me think it is an unexpected value in the data - but not sure).  Is there anyway to program around this type of error or something I need to do to avoid it?  

Thanks for any help.  

[Tom]

Presumably your real code is using a libref that is using the ODBC engine and not the WORK dataset in your example code.  What does SAS show as the type of the variable? What format does SAS show as being attached to it?  Try running PROC CONTENTS.

proc contents data=mylib.huge_data;
run;

and check how PROD_DATE is defined.

[Lost_Gary]

Tom,

Thanks - you are correct.  The set statement should show a library (set server.huge_data) as the data resides on a server.  The data field 'Prod_date' is numeric, with a format & informat of Datetime20.  Length of 8.  I've been able to query small subsets of this data - but not when I try the all of the records from a particular date.  

[ballardw]

Don't know about the ORACLE part but the DHMS function takes 4 parameters and you only have have. The parameters would be separated by commas and the DATE has to be a date literal or variable holding the date:

dhms('01JAN2020'd,00,00,00);

You should have also gotten an error similar to :

                   dhms(01JAN2020:00:00:00);
                        -------
                        388
                        76
ERROR 388-185: Expecting an arithmetic operator.

ERROR 76-322: Syntax error, statement will be ignored.

Sometimes other errors may not be reported depending on the nature. If you did not see this error be prepared.

[Lost_Gary]

sorry, I may not have listed my code appropriately for DT, but when I run this format (where prod_date = dhms('01JAN2020'd,00,00,00) I receive the same error message.  I am able to capture a little more than 300,000 records before the code bombs and gives me the error.  

[SASKiwi]

Have you tried this?

where prod_date = '01JAN2020:00:00:00'dt;

[Lost_Gary]

yes, same error. Is there anyway to get past this - like a 'force' statement or 'ignore'?

[Tom]

Sounds like you might have an issue in the remote database.   Sometimes people use extreme date values as an attempt to mimic a special missing value.  For example they might set an end date to some really large future date. Or a start date to some really old past date.  Or you could just have garbage dates.

Perhaps you can use some other tests to try to find some examples of the vlaues that are generating the error?  Perhaps by filtering with other variables also and see if you can pinpoint which observations to look at.