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

[#2]

hi,

use following

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

and following where condition

where Date(Date) > '31/12/2013'

[mazacini]

Thanks Massimo.

Simple when you know how!

[#4]

  Try this please:

LOAD [Field A],
Date
FROM [107147_Where Date To Exclude Text.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE NOT isText(Date) and Date > Date#('31/12/2013', 'DD/MM/YYYY');

The Date Field was interpreted as a TEXT because of certain values like "Not Paid"

Fabrice