I use this method because as you say the dates in SAP are usually kept as varchar. Don't forget to put the date in ' ' .
Load * ;
SQL Select VBELN, LIFNR
WHERE ( LFDAT => '20000101' ) ;
and if you want other clauses you can use the like method on characteristic values.
AND (MATERIAL LIKE 'ABCD%' OR MATERIAL LIKE 'WXYZ%') ;
If you want to convert the date format you can use this.
DATE([LFDAT],'DD/MM/YYYY') as Date // This will now be in correct date format when loaded into an App.
LEFT([LFDAT],4) AS Date_Year // This will now be in a number format when loaded into an App e.g. 2000
I know that I have to put the date in ' '. But I'm looking for a solution to convert the date to number in the where clause like the oracle command to_number. Using this I could compare the field to a number, e.g. '...where to_number(LFDAT) >= 20101015'.
It is a good hint to use LIKE to compare a part of the string. It is useful if you need a whole year or month but it is not the best solution to select several months or a special date range.
Thank you for your help but maybe someone still has another idea to solve this problem, maybe you.