September 14, 2018 at 6:53 am
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.
September 14, 2018 at 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...
September 17, 2018 at 4:55 am
DinoRS - Friday, September 14, 2018 7:15 AMTry 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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply