not able put the date filter in where clause

[#1]

Hi Guys

When i am pulling the data from navision 4.0 into qlikview with date filter in the where Clause ...

i am getting the following error :

SQL##f - SqlState: 22008, ErrorCode: 0, ErrorMsg: [Simba][SimbaEngine ODBC Driver]Invalid date, time or timestamp value.

Return_Receipt_Header:

[Siva_Sankar]

I guess it may be in some other format(qvw considered as char). Try converting to date format using date functions in qlikview and then load.

Regards.

Siva

[#3]

i want to pull the data from navision where date >= 01/01/2005;   so that before data will come into Qlikview ,,, it will get filtered..........

[#4]

Hi Manish,

   Please make sure the format of the date fieldand parameter as same.

Ex : Select * From Table Where Date(TransactionDate,'DD-MM-YYYY')=Date(Makedate(07-01-2014),'DD-MM-YYYY')

[#5]

Hi Manish,

You can check the Date Format (in Systemn variables) in qlikview and make sure that the format matches to the one in source. For Eg:

SET DateFormat ='M/D/YYYY';
SET TimestampFormat ='M/D/YYYY h:mm:ss[.fff] TT';

Or whatever format you need. Hope this helps!

[#6]

Hi manish,

Can you try with the function convert & Getdate,

on your clause "where CONVERT(Field Name,GETDATE(),110)".

it may help.

[#7]

Hi Manish,

U want to get the data filtered then U need to Put the Date format As it in Ur Database ..

Otherwise it will not get Filtered Data  and U must need to Put that String In Single Quotes..

Select * From navison where Date >= '12/12/12' ;

Hope It Helps

Regards,

Koti Reddy

[nilesh_gangurde]

Hi Manish,

You can use following two Queries:

1 )  Sql Select * from TableName where LAST_UPDATE_DATE <=    to_date('01-Jan-2014','DD-MON-RRRR HH24:MI:SS');

Or

2 ) Sql Select * from TableName where LAST_UPDATE_DATE <=    '01-Jan-2014';

-Hope this will help.

-Nilesh

[#9]

nothing is working ......

[salto]

Hi Manish,

for this I would use a variable in the script:

Set NumOfYearsTo Load = 9;

...

let MinYearLoad = Year(today()) - $(NumOfYearsToLoad);

...

and then, in the WHERE clause:

InvoiceHeader:

  LOAD "No_" as DocumentID,

"Posting Date" as DateID,

...

    where Year("Posting Date") > $(MinYearLoad);

  SQL SELECT *

  FROM Sales Invoice Header Table;

This works for my case. Hope it helps you as well!