where clause on date field

[TomBond77]

Hi experts

I need to restrict the result on a date field.

I added this where clause to my script. It run well before the addition of this clause. 

VARIABLES (
....
)
FROM [table]

WHERE

Date#([0PSTNG_DATE], 'DD.MM.YYYY') >= '01.01.2019'
;

Any ideas if the syntax of this clause is wrong?

Many thanks

Tom

[tresesco]

Date#() converts a text to date value. Therefore, if your date field has values which are already dates, you should instead use date(), like:

Date([0PSTNG_DATE], 'DD.MM.YYYY') >= '01.01.2019'
;

Else, If your date field has text values you have to convert it to date using date#() and then format it using date(), like:

Date(Date#([0PSTNG_DATE], 'textformathowtheylook'), 'DD.MM.YYYY') >= '01.01.2019'
;

[TomBond77]

Thank you

Unfortunately I doesn't work....any ideas?

[tresesco]

How do your dates look like? And how exactly did you try your expression? 

[TomBond77]

We set our SAP BW date fileds in advance of the script to this format:

SET DateFormat='DD.MM.YYYY';

Thereafter a LOAD, then SELECT , VARIABLES, FROM , WHERE clause.

[Gabriel]

Hi 

What's default format of [0PSTNG_DATE]?

Note that DATE#() is interpreter while DATE() is presentation.

The knowledge of DATE#() and DATE() will help you to filter on [0PSTNG_DATE].

Please reply to my question and community can help best with answer.