July 23, 2013 at 9:57 am
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 '2013-03-29' 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.
July 23, 2013 at 10:58 am
If you're a fan of Regular Expressions, you could do a Regex match on it to reject anything other than
NNNN-NN-NN
where N is in the range [0-9].
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 23, 2013 at 3:51 pm
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 1-9 if it is 1 or 2 then the second can be 0-9 unless the month is 02 in which case it is only 0-8 unless the year is divisible by 4 but not 100 in which case is 0-9. 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!
July 24, 2013 at 3:34 am
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 1-9 if it is 1 or 2 then the second can be 0-9 unless the month is 02 in which case it is only 0-8 unless the year is divisible by 4 but not 100 in which case is 0-9. 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply