date format issues with extractors

dataservices sap-erp-bw
1

Hi,

I am having a problem with passing date formats to sap extractors. I think this is a bug, but may be wrong.

I have two extractors both of which have a DATETO column.

Both on them contain in the queries where clause

DATETO = ‘9999-12-31’

In the first case this works. In the second case it doesn’t. I can verify the issue is related to date formatting, as if I replace the statement with

year(DATETO) = 9999

they both work !

Firstly, is there an unambiguous string I can use here ? My assumption is for some reason, even though the first one converts as YYYY-MM-DD, the second one doesn’t.

Secondly, if I use to_date to explicitly convert, won’t this prevent pushdown ? In some of my extractors we rely on the dates to limit volumes. Does it make a difference if were to do this in the start of my job and set a variable
i.e. $SAP_ENDOFTIME = to_date(‘9999-12-31’, ‘YYYY-MM-DD’)
vs in the where clause ?

Thanks for your help !

Leigh Kennedy :australia: (BOB member since 2012-01-17)

2

Version of BODS? One of the most recent fixes fixed some inconsistencies around dates for the extractors.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

3

Yes. Its DS4 SP3. Issues occurs in both SP3 base and SP3 Patch 1

Leigh Kennedy :australia: (BOB member since 2012-01-17)

4

I started documenting that in the Wiki but had a few questions myself.

As it sounds you are using an Extractor in query mode and you add a where clause there, make sure it is a >= and <= condition and the global variable used in a date datatype.

Werner Daehn :de: (BOB member since 2004-12-17)

5

Yes, I’m using the extractors in query mode.

In this case as I only want correct records, I am using = rather than a range. And it is now working by using a global variable of type date, but it used to work fine until SP3 with the strings.

Leigh Kennedy :australia: (BOB member since 2012-01-17)