Load.. where date > '31/12/13' does not exclude non-date entries

[mazacini]

Hi

I have a "date" field which contains date values in format DD/MM/YYYY.

The field also contains free format text entries.

I want to load data where my field has a date in 2014.

I am using the where  ...date > '31/12/2013'.

It successfully applies the date condition, but the free format text entries are also being loaded.

Help anyone?

EDIT: The actual contents of the free format field are note being loaded, but the other table fields for those records are being loaded- I wanted to exclude those from my load

2nd EDIT - my data is in an Excel.table - I'm not sure if that is relevant to the DD/MM/YYY format I mentioned above???

3rd Edit - Contents of Free format ARE being loaded - see attached excel

[MK_QSL]

Where date > Date('31/12/2013','DD/MM/YYYY')

[#3]

hi

try this

where  ...date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') > date(date#('31/12/2013','DD/MM/YYYY'),'DD/MM/YYYY')

[er_mohit]

Try this

where

date > Date(Date#(purgechar('31/12/2013','/'),'DDMMYYYY'),'DD/MM/YYYY')

[mazacini]

Thank you - didn't work.

No effect on load.

[mazacini]

Thank you.

Didn't work.

No data loads under this condition..

[mazacini]

Thank you.

Didn't work.

No effect on load.

[#8]

hi

try this

where

..num(.date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') )

>

num( date(date#('31/12/2013','DD/MM/YYYY'),'DD/MM/YYYY'))

[sujeetsingh]

Also check condition

   AND DATE<>null()

[maxgro]

where

IsNum(date#(Field, 'DD/MM/YYYY')) and year(date#(Field, 'DD/MM/YYYY'))>2013;

this is my script to check

Source:

LOAD * INLINE [

Field

31/12/2013

31/12/2013

31/12/2012

31/12/2013

31/12/2014

31/11/2014

30/12/2013

a

31/12/2014

31/1a/2014

31/12/2014aaa

31/12/2013

as 20/10/2014

30/12/2014

31/12/2013b

];

Table:

NoConcatenate load *

resident Source

where

IsNum(date#(Field, 'DD/MM/YYYY')) and year(date#(Field, 'DD/MM/YYYY'))>2013;

DROP Table Source;