SAp SQL Where clause to select year and month from datefield

[andy]

Hi all,

I'm trying some Oracle-syntax to filter out data for specific year and month from a SAP-table.

Any hints on how this can be done with the SAP SQL connector?

The following error occurred:
Connector reply error: RfcAbapException:SQL_ERROR
Message: The function "TO_CHAR" is unknown. Line: 4 Word: TO_CHAR(
The error occurred here:

SQL SELECT
*
FROM JCDS
WHERE TO_CHAR(UDATE,'YYYY')=2023 AND TO_CHAR(UDATE,'MM')=3

[marcus_sommer]

Take this as starting point: CAST Function (Data Type Conversion) | SAP Help Portal

[andy]

Tried all combinations of cast() and To_Date() TO_VARCHAR and UDATE+0(4) etc etc I came across without any luck

[marcus_sommer]

I don't know the various cast functions/parameter/syntax from SAP but I'm sure there are possibilities to get the wanted results. Beside a trial & error approach you may need a more systematically approach and looking on the communities for examples (you won't be the first one with this challenge). Nevertheless if you want to remain by trial & error you shouldn't do it within the where-clause else applying it within the query to see the resulting output which may in the next time a bit adjusted here and there ...

Beside this if this field is an origin date-field in SAP and you want just to fetch year and month from it you may look for such functions. I would be very surprised if SAP hasn't ones.

[andy]

Hi,

I fully agree with you, just want to make clear that what I've been trying is all kind of cast,to_varchar, to_date, extract and substring functions and I've tried them in the way as exemplified below. Googling a lot to find the right syntax but I haven't found a syntax that works through the Qlik SAP Connector so haven't been able to take it further. Always an error-message from the connector. I'm sure it is a simple one when you know, as it always is, so therefore I thought I should as here.

Never mind, I left the problem behind and now read the table incrementally with the drawback that the qvd is one of the bigger ones. Code below is a snippet automatically generated by my Extract-script. I was just curious of how it could be solved but have to move on. Thanks for spending your time trying to guide in right direction.

JCDS_new:
LOAD 
	[CDTCODE] as [Transaction_CDTCODE],
	[CHGNR] as [Change number_CHGNR],
	[CHIND] as [ChangeIndicator_CHIND],
	[INACT] as [Status Inact._INACT],
	[MANDT] as [Client_MANDT],
	[OBJNR] as [Object number_OBJNR],
	[STAT] as [Status_STAT],
	[TCODE] as [Transaction code_TCODE],
	[UDATE] as [Date_UDATE],
	[USNAM] as [User_USNAM],
	[UTIME] as [Time_UTIME],
	[_DATAAGING] as [__DATAAGING],
	autonumber(MANDT&OBJNR,'JCDS') as PK_Inc;
SQL SELECT
	*
FROM JCDS
WHERE  UDATE>='20230308';