Date Validation with ODBC and File

sam334 · Post by **sam334** » Tue Oct 13, 2015 9:05 am

All,
One more question on date validation. We have a job which outputs a cvs file. I created another job, which will read the file and Join with a Query from SQL server. Both have date filed. (File and query).

How we can have a validation check to see both the date are equal or both the dates are same as parameter date.

So, if any how csv file date is 10/11 and odbc date is 10/12. Job should abort.

Any thoughts.

Design-

Code: Select all

      odbc
       |
SEQ---JOIN---SEQ

chulett · Post by **chulett** » Tue Oct 13, 2015 9:19 am

Either make sure they're all in the same string format or convert them to a date first. StringToDate() comes to mind for the latter. After that, just... compare them.

sam334 · Post by **sam334** » Tue Oct 13, 2015 9:34 am

Craig, I did first part. What should I do to compare it. Any functions.

I mean, as the design has Join stage. We cant compare in Join right, it has to be in transformer. Kind of confused.

chulett · Post by **chulett** » Tue Oct 13, 2015 10:14 am

You haven't stated what your join criteria is. I'm guessing that if your date is part of it then you could consider a join failure to mean "not equal". Otherwise, yes... simply compare them in a transformer. Is all you need to know is if they are equal or not equal? If so, then the string format doesn't matter as long as they are all the same. However, if you want to know if one is less or great than another, you would need to use strings in a format like "YYYYMMDD" so any comparison would work properly. Now, if you are comparing them as DATE fields, then there is no format to worry about and again, all comparisons should work. No special functions required.

sam334 · Post by **sam334** » Tue Oct 13, 2015 11:26 am

Craig, Sorry for that. I am joining on ID, not the date. Did it and worked fine. Thanks a lot for the explanation.