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].
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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 thanksI 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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy