topic Re: Using Date Filter in Qlikview Script in QlikView

Using Date Filter in Qlikview Script

Fri, 12 Apr 2013 15:25:33 GMT

I'm trying to pull some data from an Oracle database using date filter, but I'm not avble to do so in Qlikview Script window. Date Format in Source Database is "3/25/2012 11:07:00 AM". I want to pull all records from 1st Jan 2013. Please advise..........

SQL: SELECT "ACK_DATE","BOX_NO","BOX_SHIP_DATE",FROM "ABC_DB"."ORDER_DETAIL" Where "ENTRY_DATE">= '1/1/2013 00:01:00 AM';

Re: Using Date Filter in Qlikview Script

Gysbert_Wassenaar — Fri, 12 Apr 2013 15:43:35 GMT

You probably have to use the to_date() function to create a date from the string:

SELECT "ACK_DATE","BOX_NO","BOX_SHIP_DATE" FROM "ABC_DB"."ORDER_DETAIL" Where "ENTRY_DATE">= to_date('1/1/2013 00:01:00 AM','M/D/YYYY hh:mi:ss AM');

Re: Using Date Filter in Qlikview Script

eddysanchez — Fri, 12 Apr 2013 16:08:06 GMT

After the SQL statement the SELECT sentence is compiled for the provider (Oracle)

So you need to do something like that:

SQL SELECT ACK_DATE,BOX_NO,BOX_SHIP_DATE

FROM ABC_DB.ORDER_DETAIL

Where ENTRY_DATE>= '20130101';

Re: Using Date Filter in Qlikview Script

Mon, 15 Apr 2013 13:18:35 GMT

Hi Eddy, tried your sujjestions but I'm getting an error message:

"SQL##f - SqlState: S1000, ErrorCode: 1861, ErrorMsg: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string"

Re: Using Date Filter in Qlikview Script

Mon, 15 Apr 2013 13:30:19 GMT

I get this error Message:

"SQL##f - SqlState: S1000, ErrorCode: 1821, ErrorMsg: [Oracle][ODBC][Ora]ORA-01821: date format not recognized"

Re: Using Date Filter in Qlikview Script

Tue, 16 Apr 2013 10:18:18 GMT

Hi All, tried the below mentioned format and its working Now:

* Where ENTRY_DATE>= TO_DATE('2013-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')