How to filter Db2 dates in SAS dataset?

[Babloo]

I've the following code to filter values in Db2 table but I end up with error like "CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0103N The numeric literal "31MAR2018" is not valid. SQLSTATE=42604"

Value of macro variable REPORTING_DATE resolves to 31MAR2018. Could you please help me to resolve this issue?

select * 
	    from connection to &glb_db2. 
			( 
			SELECT DISTINCT
			D1IPAR,
			MTD
	     	FROM U.TBFQ81
			WHERE D1NDBT = 'TBFR22' and PDK = '4' AND D1IPAR is not null and MTD=&REPORTING_DATE.
			)
        ;

[Kurt_Bremser]

DB/2 uses a different format for date literals. Ask your DB/2 people how you need to provide the date in the pass-through. Or do a google search for "db2 date literal".

[Babloo]

Sure. Just to inform you that I could see the DB2 table (might be
registered) in SAS DI Studio and it has the value 31MAR2018 (which I'm
looking for) and it has date9. format.

[Kurt_Bremser]

---

@Babloo wrote:
Sure. Just to inform you that I could see the DB2 table (might be
registered) in SAS DI Studio and it has the value 31MAR2018 (which I'm
looking for) and it has date9. format.

---

That's because SAS uses its own display formats in its world. But in an explicit pass-through, you have to adhere to the rules of the DBMS.

[LinusH]

Using explicit pass through is over complicating nd should only be used when performance improvements can be expected, which is not the case here.
Use a libname connection and you don't have to bother about target RDBMS date formats.

[Babloo]

I was asked to use explicit pass-through.

[Kurt_Bremser]

---

@Babloo wrote:
I was asked to use explicit pass-through.

---

Then you have to use DB/2 syntax, period.

BTW as a SAS developer, it is often your job to make it clear to persons who have no clue (at least about SAS) which solution is better. Just that somebody says "do it this way" does in no way implicate they know what they're talking about. In fact, people trying to force you to do something in their preferred way is usually a sign of galloping stupidity on their side.

[Patrick]

---

@Babloo wrote:

I've the following code to filter values in Db2 table but I end up with error like "CLI describe error: [IBM][CLI Driver][DB2/LINUXX8664] SQL0103N The numeric literal "31MAR2018" is not valid. SQLSTATE=42604"

Value of macro variable REPORTING_DATE resolves to 31MAR2018. Could you please help me to resolve this issue?

select * 
	    from connection to &glb_db2. 
			( 
			SELECT DISTINCT
			D1IPAR,
			MTD
	     	FROM U.TBFQ81
			WHERE D1NDBT = 'TBFR22' and PDK = '4' AND D1IPAR is not null and MTD=&REPORTING_DATE.
			)
        ;

---

Like in SAS you need to tell the DB2 that the string you're passing in needs to get converted to a datetime value. Just Googling in quickly it appears that DB2 has a TO_DATE() function for this.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r000710...