SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TRY_PARSE


TRY_PARSE

Author
Message
Matthew McGiffen
Matthew McGiffen
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 158
Comments posted to this topic are about the item TRY_PARSE
Andy Robertson
Andy Robertson
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3145 Visits: 587
Nice article. Thanks
I would outright refuse to use data in a column that seems to be date data but has no consistent formatting. The problem is that you don't know whether some cells are dmy or mdy and even worse if the year is 2 digit and the data is from 2001 to 2012!
Totally agree that Try_Convert and Try_Cast tend to be the best functions to use for these sorts of conversions.
DinoRS
DinoRS
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 376
Try BI Development then Andy, I just had 3 days ago a flat file on my table with dates in length of 3 to 6 Digits (102 = 20000102, wee!), usual Extract has about 6 - 7 Steps before things get into the first table, with this I had 9 steps just to cleanse the date into a useable date. And better not to mention the fact that you might have Pre-2000 Data so you can't simply ("20"+ [ColName]) for that case either...
Matthew McGiffen
Matthew McGiffen
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 158
@DinoRS I feel your pain!
Andy Robertson
Andy Robertson
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3145 Visits: 587
DinoRS - Friday, September 14, 2018 7:15 AM
Try BI Development then Andy, I just had 3 days ago a flat file on my table with dates in length of 3 to 6 Digits (102 = 20000102, wee!), usual Extract has about 6 - 7 Steps before things get into the first table, with this I had 9 steps just to cleanse the date into a useable date. And better not to mention the fact that you might have Pre-2000 Data so you can't simply ("20"+ [ColName]) for that case either...

Bad luck! I guess it depends on your requirements, but the old saying garbage in garbage out clearly applies. If you have to import this then fair enough but then you have to specify that the date data is not reliable in your output reports. I do a lot of BI development and I would kick up a huge fuss if I received data like this and make sure everyone knows that it will not be reliable and what the exact conversion rules will be. I'd even be throwing out a distinct list of unreliable dates that could be 2nd of jan or first of feb etc for them to make a decision on.
The problem is that as the BI developer they will blame you when the reports are rubbish not the person that gave you the file.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search