

Mr or Mrs. 500
Group: General Forum Members
Last Login: Thursday, August 4, 2016 5:03 AM
Points: 548,
Visits: 863


Hi Guys, Is there a simple solution to this.
I have a flat file which is being generated by a 3rd party. They are supposed to pass me dates in the format '20130329' but in a recent file the date was actually '29 MAR 2013' which the system sucessfully parsed and pushed through the data pipe, however I want to error if the data is not in the specified format.
Short of reading it in as a string (rather than a date) and then deriving the dateparts is there a simple way to reject these lines. ( have 48 files to import with around 100 date fields and I don't really want to run this derivation on them all, even if it ends up being a CLR function.




SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 7,430,
Visits: 17,999


If you're a fan of Regular Expressions, you could do a Regex match on it to reject anything other than
NNNNNNNN
where N is in the range [09].
Help us to help you. For better, quicker and morefocused answers to your questions, consider following the advice in this link.
When you ask a question (and please do ask a question: "My TSQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Never attribute to malice that which is adequately explained by stupidity




Mr or Mrs. 500
Group: General Forum Members
Last Login: Thursday, August 4, 2016 5:03 AM
Points: 548,
Visits: 863


Phil, Thanks but no thanks :)
I hate regex. The regex would need to treat it as a string (either explicitly or cast in a derived column) and then apply rules such as
1st digit of month can only be 0 or 1. If it is 0 then the second digit can only be 1 thru 9. If the first digit is a 1 then the 2nd digit can only be a 0,1 or 2.
The 1st digit of the day can only be 0,1,2,3. If it is 0 then the second can only be 19 if it is 1 or 2 then the second can be 09 unless the month is 02 in which case it is only 08 unless the year is divisible by 4 but not 100 in which case is 09. If the first digit is 3 then the second can only be 0 or 1 unless the month is 02 in which case it is invalid!




SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 7,430,
Visits: 17,999


aaron.reese (7/23/2013) Phil, Thanks but no thanks :)
I hate regex. The regex would need to treat it as a string (either explicitly or cast in a derived column) and then apply rules such as
1st digit of month can only be 0 or 1. If it is 0 then the second digit can only be 1 thru 9. If the first digit is a 1 then the 2nd digit can only be a 0,1 or 2.
The 1st digit of the day can only be 0,1,2,3. If it is 0 then the second can only be 19 if it is 1 or 2 then the second can be 09 unless the month is 02 in which case it is only 08 unless the year is divisible by 4 but not 100 in which case is 09. If the first digit is 3 then the second can only be 0 or 1 unless the month is 02 in which case it is invalid!
But it would trap the case you mentioned very easily. If you had included all of these additional requirements in your original post, I would not have suggested it.
Help us to help you. For better, quicker and morefocused answers to your questions, consider following the advice in this link.
When you ask a question (and please do ask a question: "My TSQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Never attribute to malice that which is adequately explained by stupidity



