Forum Discussion

dtaylor1's avatar
dtaylor1
New Contributor
5 years ago

Diff shows identical rows as modified

I am using a Diff Snap against two inputs in which the rows look like below, and the Snap is outputting to the Modified output View. I have found it’s because of the dates, meaning, I have rows without Dates, and they end up in the Unmodified Output View. I have been wracking my brain (I am bald, so no hair to pull out) as to why, it looks like the rows are completely identical! Please, if anyone has any ideas, I’d be grateful!

Row Sample Data (These are taken from the SQL Server Select Output Views in Raw Format, after a Validation:

{“SourcePersonID”:“0009664”,“CardID”:“0009664”,“TransactionType”:“3”,“BalanceAmount”:“31.70”,“CurrentDate”:“2019-08-23T14:34:42.000”}
{“SourcePersonID”:“0009664”,“CardID”:“0009664”,“TransactionType”:“3”,“BalanceAmount”:“31.70”,“CurrentDate”:“2019-08-23T14:34:42.000”}

3 Replies

  • cstewart's avatar
    cstewart
    Former Employee
    5 years ago

    Are they from the same type of source, has one been persisted as JSON and one read directly from a source (like a database?).

  • ptaylor's avatar
    ptaylor
    Employee
    5 years ago

    Do you see the same behavior if you hold the Shift key when you do the Validation?

  • dtaylor1's avatar
    dtaylor1
    New Contributor
    5 years ago

    Thanks for the replies.

    In the end, I figured it out. I have the date as a VarChar(50) data type field coming from a staging table, in which the date is of the form, “2019-08-23 14:34:42” (without the quotes), and a destination table with a DateTime2(7) data type, so the date is of the form “2019-08-23 14:34:42.0000000” (again, without the quotes).

    I was getting what looked like identical results, in the JSON Output views of each query, after I applied the formula Date.parse($CurrentDate).toLocaleDateTimeString() to the stage table date, and was doing nothing to the destination table’s date.

    What made it work was applying the formula $CurrentDate.toString() to the DateTime2(7) value, and then suddenly the Diff Snap had only Unmodified rows. The data still looks exactly the same in both output views before the Diff, which tells me that what looks the same is not necessarily the same internally to Snaplogic.

    Thanks again!
    David