Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Problem with SQL and date format

In the attached QVW I'm trying to extract data in the Orderbook tab where the snapshot_date is within the last 365 days.

I've created a variable vToday which is currently set on 90 to test but it still extracts data back to 23/06/2015

Even when I hard code the extract to extract where snapshot_date > '01/10/2015' is still pulls from 23/06/2015

The only was I can get this to work is to hard code with 10/01/2015, i.e. transposing the month and day so using MM/DD/YYYY

All the data coming out of SQL server is in DD/MM/YY format so I don't understand why this is.

Thanks in advance.

1 Solution

Accepted Solutions

Re: Problem with SQL and date format

May bad, try this:

LET vToday = Date(floor(Today())-90,'MM/DD/YYYY');

LOAD

date(floor(snapshot_date), 'DD/MM/YYYY') as snapshot_date,

warehouse as orderbook_warehouse,

HPC_due_date as orderbook_due_date,

required_date as orderbook_required_date,

customer as orderbook_customer,

product as orderbook_product,

order_qty as orderbook_qty,

order_book_value as orderbook_value,

snapshot_date as orderbook_snapshot_date,

IF(HPC_due_date <= snapshot_date,'Y','N') as orderbook_arrears;

ODBC CONNECT TO [Sage PE Live];

SQL SELECT *

FROM pelive.dbo."hpc_op_orderbook_history"

where HPC_due_date < snapshot_date+90

and snapshot_date > '$(vToday)';

3 Replies

Re: Problem with SQL and date format

Can you try reloading with this:

LET vToday = Date(floor(Today())-90,'DD-MMM-YYYY');

LOAD

date(floor(snapshot_date), 'DD/MM/YYYY') as snapshot_date,

warehouse as orderbook_warehouse,

HPC_due_date as orderbook_due_date,

required_date as orderbook_required_date,

customer as orderbook_customer,

product as orderbook_product,

order_qty as orderbook_qty,

order_book_value as orderbook_value,

snapshot_date as orderbook_snapshot_date,

IF(HPC_due_date <= snapshot_date,'Y','N') as orderbook_arrears;

ODBC CONNECT TO [Sage PE Live];

SQL SELECT *

FROM pelive.dbo."hpc_op_orderbook_history"

where HPC_due_date < snapshot_date+90

and snapshot_date > '$(vToday)';

Re: Problem with SQL and date format

May bad, try this:

LET vToday = Date(floor(Today())-90,'MM/DD/YYYY');

LOAD

date(floor(snapshot_date), 'DD/MM/YYYY') as snapshot_date,

warehouse as orderbook_warehouse,

HPC_due_date as orderbook_due_date,

required_date as orderbook_required_date,

customer as orderbook_customer,

product as orderbook_product,

order_qty as orderbook_qty,

order_book_value as orderbook_value,

snapshot_date as orderbook_snapshot_date,

IF(HPC_due_date <= snapshot_date,'Y','N') as orderbook_arrears;

ODBC CONNECT TO [Sage PE Live];

SQL SELECT *

FROM pelive.dbo."hpc_op_orderbook_history"

where HPC_due_date < snapshot_date+90

and snapshot_date > '$(vToday)';

Not applicable

Re: Problem with SQL and date format

That's great, works a treat.

so basically transpose the date at the top, but the vital thing I had missed was the single speech marks around $(VToday)

Community Browser