# Date Column has some rows with NULL - strict\_date\_optional\_time causes Exception

**URL:** <https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164>\
**Category:** Elasticsearch\
**Created:** [December 31, 2023, 8:14pm UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164 "2023-12-31T20:14:27Z")\
**Posts on this page:** 1\
**Showing post:** 5

<div class="post-metadata">

**Author:** ![Ethan777100](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ethan777100/32/70366_2.png) [@Ethan777100](https://discuss.elastic.co/u/Ethan777100)\
**Post date:** [January 1, 2024, 4:41am UTC](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164/5 "2024-01-01T04:41:41Z")

</div>

@stephenb RE: [Unable to convert [0.0] to long - Elastic Stack / Elasticsearch - Discuss the Elastic Stack](https://discuss.elastic.co/t/unable-to-convert-0-0-to-long/350031/10)

My reply to you i think more relevant in this thread.

I can't see any difference. I ran the docker container logstash, opened the logs trail in wsl terminal.

Same date time parsing error. Nothing changed.

```auto
        "original" => "1033718,NULL,0.0,0.0,0.0,2022-12-27 04:03:12.526,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:35:29.11,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:35:29.595,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-02-21,NULL,2023-03-04,PM000000098438,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 04:03:13.167,126692,0,NULL,NULL,2023 PWY-ML-IS/12,NULL,NULL,2023-02-21 00:00:00.0,2023-02-21 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:34:02.356,NULL,131758.0,0,NULL,2023-02-23 00:00:00.0,NULL,2023-02-19 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428934,0,WO00428934,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221"
    },
       "message" => "1033718,NULL,0.0,0.0,0.0,2022-12-27 04:03:12.526,0.0,NULL,NULL,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,126692,2022-12-30 16:35:29.11,0,NULL,NULL,NULL,NULL,PWY_PQA,NULL,2023 PWY PQA 11-Daily xxx General Inspection,NULL,0,0,NULL,NULL,NULL,0,0,0.0,0.0,0.0,0.0,3.0,0.0,0.0,0.0,0.0,NULL,NULL,NULL,NULL,NULL,WSCH,NULL,0,0,NULL,NULL,NULL,NULL,NULL,0,0,0,0,NULL,NULL,0,1,NULL,NULL,1,NULL,0,0,NULL,JP00008836,NULL,NULL,EN,2022-12-30 16:35:29.595,NULL,NULL,0,L000000000009,0,NULL,NULL,NULL,0,WORKORDER,NULL,NULL,NULL,NULL,NULL,NULL,xxxMRT,NULL,NULL,0.0,NULL,NULL,NULL,NULL,1,0,PWY,11.0,DAYS,0,0.0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2023-02-21,NULL,2023-03-04,PM000000098438,NULL,NULL,NULL,NULL,NULL,NULL,2022-12-27 04:03:13.167,126692,0,NULL,NULL,2023 PWY-ML-IS/12,NULL,NULL,2023-02-21 00:00:00.0,2023-02-21 00:00:00.0,NULL,0,xxx,NULL,NULL,APPR,2022-12-30 16:34:02.356,NULL,131758.0,0,NULL,2023-02-23 00:00:00.0,NULL,2023-02-19 00:00:00.0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,WORKORDER,WO00428934,0,WO00428934,NULL,NULL,NULL,PM,NULL,2023-03-08 16:07:17.438,0160a289-ee6a-4dca-94f7-4b3794caf1a7,FAIL,20230308,20230221",

```

Now, I made the rest of the date fields into `keyword` so that I don't get this date issue.

Only the `@timestamp` and `r_xxx_mms_ar_workorder_xt.reportdate` column is date format. Timestamp is inherited from `r_xxx_mms_ar_workorder_xt.reportdate` as per the interface you taught me in Nov 2023.

Even so, I did not change the date/time formats available. My time stamps here match `yyyy-MM-dd HH:mm:ss.SSS`

```auto
"format": "strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S",

```

And still I get a date-time parsing error over a column that I clearly redefined to `keyword` (`r_xxx_mms_ar_workorder_xt_actfinish`) and re-consoled the updated index and mapping template? This confuses me

```auto
error"=>{"type"=>"document_parsing_exception", "reason"=>"[1:7102] failed to parse field [r_dtl_mms_ar_workorder_xt_actfinish] of type [date] in document with id 'hj5Cw4wBRHaUpJXLebEA'. Preview of field's value: 'NULL'", "caused_by"=>{"type"=>"illegal_argument_exception", "reason"=>"failed to parse date field [NULL] with format [strict_date_optional_time||yyyy-MM-dd HH:mm:ss.SSS||yyyy-MM-dd HH:mm:ss.SS||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S]", "caused_by"=>{"type"=>"date_time_parse_exception", "reason"=>"Failed to parse with all enclosed parsers"}}}}}}

```

---

_[View the full topic](https://discuss.elastic.co/t/date-column-has-some-rows-with-null-strict-date-optional-time-causes-exception/350164)._