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

[manideep78]

where Year(Date(Date#(date_field , '<your existing date field format>'),'DD/MM/YYYY')) = 2014

[mazacini]

Hi.

Thanks for that again.

But still no data loading!

[mazacini]

Thank you..

Did not work.

[mazacini]

Thanks Massimo

I inserted your where condition and no data loads!

See my 2nd Edit re Excel???

Joe

[jonathandienst]

Hi

It seems that the format of the dates in your data (DD/MM/YYYY) is not the same as the default for your model/locale.

... Where Date#(DateField, 'DD/MM/YYYY') > Date#('31/12/2013', 'DD/MM/YYYY');

But you can replace Date#('31/12/2013', 'DD/MM/YYYY') with a date literal written in the default format of your model.

Example:

Assuming the following in the environment Set statements at teh top of your script:

     Set DateFormat = 'YYYY/MM/DD';

Then

... Where Date#(DateField, 'DD/MM/YYYY') > '2013/12/31';

HTH

Jonathan

[maxgro]

post the excel, just the date field

[mazacini]

Excel file attached

[mazacini]

Hi Jonathan

My date format is 'DD/MM/YYYY'.

So I think formats are consistent?

[maxgro · Accepted solution]

this script loads 10 rows,

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

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

SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

SET DayNames='lun;mar;mer;gio;ven;sab;dom';

Directory;

LOAD [Field A],

     Date,

     [Memo: Loads]

FROM

[Where Date To Exclude Text.xlsx]

(ooxml, embedded labels, table is Sheet1)

where

year(Date) > 2013

;

Date	Field A	Memo: Loads
02/01/2014	1006	y
02/01/2014	1007	y
02/01/2014	1009	y
02/01/2014	1010	y
07/01/2014	1012	y
10/01/2014	1011	y
13/01/2014	1013	y
13/01/2014	1015	y
13/01/2014	1016	y
13/01/2014	1017	y

[MK_QSL]

LOAD [Field A],

     Date,

     [Memo: Loads]

FROM

TableName

(ooxml, embedded labels, table is Sheet1)

Where Date > Date('31/12/2013') or

Date = 'Paid Cash';