Search code examples
sqlapache-drillto-date

Drill Casting to date gives wrong values

I have started using Drill date conversion function and read this link

So i was trying to imlement TO_DATE(<field>, <format>)

I wrote this query:

SELECT  DRILL_TABLE_214.date_id ,
       (DRILL_TABLE_214.date_id / 100) dup_by_100,
       CAST((DRILL_TABLE_214.date_id / 100) as varchar)  dup_by_100_with_casting,
       to_date(CAST((DRILL_TABLE_214.date_id / 100) as varchar),'yyyymmdd') `DATE_DAY` ,
       clicks DRILL_TABLE_214_MES_CLICKS 
FROM
    s3.dss.`acc=123/run-num-id=214` DRILL_TABLE_214 
WHERE
    ( DRILL_TABLE_214.date_id >= 2017040100 AND DRILL_TABLE_214.date_id <= 2017063023 )

I was trying to figure out why Does Drill convert the month to January in the To date function - but its unclear .

can you help me verify why ?Date conversion is invalid

i also tried converting this value:

to_date('2017-03-13','yyyy-mm-dd')  xxxx ,

But i got:

2017-01-13T00:00:00.000+02:00

Anyone faced the same issue ?

Solution

  • should be MM for month not mm for minutes