PROC SQL Using Date9. on a Where Condition

[newboy1218]

Hi.. In my proc sql query, I want to have a where condition 

where my_dt = '31DEC9999'

However, in the dataset, "my_dt" is a numeric value, and I got the error message

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3535)A character
       string failed conversion to a numeric value.

Do you know how I can use date9. format condition?

[novinosrin]

where my_dt = '01JAN2019'd

[newboy1218]

I tried that too, but I still got an error message

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3706)Syntax error:
expected something between a string or a Unicode character literal and the word 'd'.

[Tom]

---

@newboy1218 wrote:

I tried that too, but I still got an error message

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3706)Syntax error:
expected something between a string or a Unicode character literal and the word 'd'.

---

Show the full code. You cannot use SAS syntax for date literals in code that you are explicitly passing into Teradata. You need to use Teradata syntax in that part of the code.

libname mylib teradata .... ;

data test1;
  set mylib.mydata ;
  where mydate = '01AUG2019'd ;
run;

proc sql ;
connect using mylib ;
create table test2 as select * from mylib
 (select * from mydata where mydate = DATE'2019-08-01')
;
quit;

[tsap]

Put the letter 'd' after the value you are supplying.

For example:
where my_dt = '01JAN2019'd

[ballardw]

---

@newboy1218 wrote:

Hi.. In my proc sql query, I want to have a where condition 

where my_dt = '01JAN2019'

However, in the dataset, "my_dt" is a numeric value, and I got the error message

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database](-3535)A character
       string failed conversion to a numeric value.

Do you know how I can use date9. format condition?

---

The entire Proc SQL code would help. As it is we have to ask is this pass through to Teradata or not.

If the date is supposed to be treated as a SAS date value the code likely should be

where my_dt = '01JAN2019'd

to indicate a SAS date value. But you may need to pass a Teradata date string, which I am not familiar with.

So, please post the entire Proc Sql including the connection details. XXX out passwords, user names or sensitive values, but the whole code may be needed to determine what you want.