Problem with SQL and date format

[#1]

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.

[sunny_talwar]

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)';

[sunny_talwar · Accepted solution]

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)';

[#4]

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)