Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Flat File Source: prevent records from loading with a valid date but in the wrong format Expand / Collapse
Author
Message
Posted Tuesday, July 23, 2013 9:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
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.

Post #1476691
Posted Tuesday, July 23, 2013 10:58 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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].




Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1476720
Posted Tuesday, July 23, 2013 3:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 25, 2014 2:48 AM
Points: 386, Visits: 623
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!



Post #1476818
Posted Wednesday, July 24, 2013 3:34 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 4,977, Visits: 11,669
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1476939
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse