You could write a UDF. Or you could do something like this: cat data.csv 05/25/2016 20160525 May 25th 2016
0: jdbc:drill:> select case when columns[0] similar to '__/__/____' then to_date(columns[0],'MM/dd/yyyy') when columns[0] similar to '________' then to_date(columns[0],'yyyyMMdd') else NULL end from `data.csv`; +-------------+ | EXPR$0 | +-------------+ | 2016-05-25 | | 2016-05-25 | | null | +-------------+ 3 rows selected (0.4 seconds) 0: jdbc:drill:> -Veera On Wed, May 25, 2016 at 11:12 AM, Vince Gonzalez <vgonza...@mapr.com> wrote: > Sounds like a job for a UDF? > > You could do the try/catch inside the UDF. > > ---- > Vince Gonzalez > Systems Engineer > 212.694.3879 > > mapr.com > > On Wed, May 25, 2016 at 11:05 AM, John Omernik <j...@omernik.com> wrote: > > > I have some DOBs, and some fields are empty others apparently were filled > > by trained monkeys, but while most data is accurate, some data is not. > > > > As you saw from my other post, I am trying to get the age for those DOBs > > that are valid... > > > > My function works, until I get to a record that is not valid and I get > > something like this: > > > > Error: SYSTEM ERROR: IllegalFieldValueException: Value 0 for monthOfYear > > must be in the range [1,12] > > > > > > Is there a good "Try -> Except" type solution that will grant me the > valid > > data if things worked, and just return 0 or whatever I specify if there > is > > an error? > > > > I could try casting the data, but if it fails won't it kill my query? > > Basically I want it to keep going if it fails... not sure if Drill has > this > > ability, but thought I would ask. > > > -- Veera Naranammalpuram Product Specialist - SQL on Hadoop *MapR Technologies (www.mapr.com <http://www.mapr.com>)* *(Email) vnaranammalpu...@maprtech.com <naranammalpu...@maprtech.com>* *(Mobile) 917 683 8116 - can text * *Timezone: ET (UTC -5:00 / -4:00)*
