none
in USQL LEFT ANTISEMIJOIN DateTime Compare not Working

    Question

  • HI All,

    in LEFT ANTISEMIJOIN DateTime Compare not Working, it's work for int and string.

    below is code

    i try to compare OrderDate it's  not give any error but retuerns all data from both rowset

    USE DATABASE Test;
    DECLARE @IN = "Table.csv";
              

    @newfile=
    EXTRACT
     OrderID int,
        CustomerID int,
        OrderDetailID int,
        OrderTotal double,
        OrderDate DateTime
     
     FROM @IN
          USING Extractors.Csv();



    @out=
    SELECT OrderID ,
        CustomerID ,
        OrderDetailID ,
        OrderTotal ,
        Convert.ToDateTime(OrderDate) AS OrderDate
       
    FROM dbo.Orders;

    @emps_notin_valid_dept =
        SELECT n.OrderID,
               n.CustomerID,
               OrderDetailID,
               n.OrderTotal,
               n.OrderDate
        FROM @newfile AS n
             LEFT ANTISEMIJOIN
             (
             SELECT OrderID,
                    CustomerID,
                    OrderDetailID,
                    OrderTotal,
                   Convert.ToDateTime(OrderDate) AS OrderDate
             FROM @out
                  ) AS d
             ON n.OrderID == d.OrderID AND n.CustomerID == d.CustomerID AND n.OrderDetailID == d.OrderDetailID AND n.OrderTotal == d.OrderTotal AND  n.OrderDate == d.OrderDate;
     


    OUTPUT @emps_notin_valid_dept
    TO "Orders.csv"
    USING Outputters.Csv();

    Data

    Table.csv

    1 1 1 23098.9 2016-01-01T00:00:00.0000000Z
    3 1 3 535 2016-01-02T00:00:00.0000000Z
    2 1 2 2456.99 2016-01-01T00:00:00.0000000Z
    4 2 1 500 2016-01-02T00:00:00.0000000Z
    5 3 4 40.76 2016-01-02T00:00:00.0000000Z


    Orders.csv

    1 1 1 23098.9 2016-01-01T05:30:00.0000000+05:30
    3 1 3 535 2016-01-02T05:30:00.0000000+05:30
    2 1 2 2456.99 2016-01-01T05:30:00.0000000+05:30
    4 2 1 500 2016-01-02T05:30:00.0000000+05:30
    5 3 4 40.76 2016-01-02T05:30:00.0000000+05:30

    Regards,

    Manish

    Sunday, December 23, 2018 8:21 AM

All replies

  • Hello Manish,

    Do you have any findings on this one?

    Thursday, January 3, 2019 10:48 PM
    Moderator
  • Manish, a couple of questions...

    1) The EXTRACT you've defined declares the OrderDate as Datetme, why are you doing a Convert.ToDateTime() on it again in the SELECTs?

    2) Your LEFT ANTISEMIJOIN is to a "dbo.Orders" table - what does that schema look like?  Is that same column also a DateTime?

    I see where your output for the Order.csv includes the UTC offset, maybe you can neutralize the time portion by coercing the OrderDate into a string...?


    Bill Blakey

    Friday, January 4, 2019 5:13 PM