3 Replies Latest reply: Apr 28, 2016 4:20 AM by Steve Hutchison RSS

    Problem with SQL and date format

    Steve Hutchison

      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.

        • Re: Problem with SQL and date format
          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)';

          • Re: Problem with SQL and date format
            Sunny Talwar

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