# Date format parsing exception **URL:** \ **Category:** Elasticsearch\ **Created:** [September 18, 2017, 2:56pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892 "2017-09-18T14:56:50Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![swaplogstash](https://avatars.discourse-cdn.com/v4/letter/s/e79b87/32.png) [@swaplogstash](https://discuss.elastic.co/u/swaplogstash)\ **Post date:** [September 18, 2017, 2:56pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892/1 "2017-09-18T14:56:50Z")
I have create\_date column as timestamp in database and need create\_date value in elastic as "MM/DD/YYYY" or "yyyy-MM-dd" . I am getting below error exception while loading data from oracle database using logstash 2.4. SQL: select to\_date(to\_char(cast(create\_date as date),'MM/DD/YYYY') ,'MM/DD/YYYY') from table X. exception. "error"=\>{"type"=\>"mapper\_parsing\_exception", "reason"=\>"failed to parse [create\_date]", "caused\_by"=\>{"type"=\>"illegal\_argument\_exception", "reason"=\>"Invalid format: "2015-01-21T05:00:00.000Z" is malformed at "T05:00:00.000Z""}}}}, date mapping in elastic is as below. createddt": { "type": "date", "format": "yyyy-MM-dd" } let me know how to load date in elastic. ---
**Author:** ![Rohithnama](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@Rohithnama](https://discuss.elastic.co/u/Rohithnama)\ **Post date:** [September 18, 2017, 3:20pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892/2 "2017-09-18T15:20:41Z")
Invalid format: "2015-01-21T05:00:00.000Z" is malformed at "T05:00:00.000Z""}}}}, You are passing all the way through the seconds but only specifying the date part of it (YYYY-MM-dd). In your case, you should be using a different time stamp. The following could be the possible fit for you: (As given docs) basic\_ordinal\_date\_time: A formatter for a full ordinal date and time, using a four digit year and three digit dayOfYear: yyyyDDD'T'HHmmss.SSSZ. Please refer this link to know the best fit for your case: [https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html#custom-date-formats](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-date-format.html#custom-date-formats) If you only want to use YYYY-MM-dd you might want to parse the time stamp and eliminate the other part except the standard date. Let me know if this is not the case. ---
**Author:** ![swaplogstash](https://avatars.discourse-cdn.com/v4/letter/s/e79b87/32.png) [@swaplogstash](https://discuss.elastic.co/u/swaplogstash)\ **Post date:** [September 18, 2017, 5:54pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892/3 "2017-09-18T17:54:55Z")
I need only date format in elastic such as YYYY-MM-dd or MM/DD/YYYY without time , used trunc in sql to get only date. trunc(to\_date(to\_char(cast(create\_date as date),'MM/DD/YYYY') ,'MM/DD/YYYY')). Which date format we need to use elastic mapping for date property? ---
**Author:** ![Rohithnama](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@Rohithnama](https://discuss.elastic.co/u/Rohithnama)\ **Post date:** [September 18, 2017, 6:14pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892/4 "2017-09-18T18:14:28Z")
"yyyyMMdd" This format (basic\_date) worked for me and I think your initial format should work as well. Do give a try once with both! ---
**Author:** ![swaplogstash](https://avatars.discourse-cdn.com/v4/letter/s/e79b87/32.png) [@swaplogstash](https://discuss.elastic.co/u/swaplogstash)\ **Post date:** [September 18, 2017, 6:25pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892/5 "2017-09-18T18:25:35Z")
> [@Rohithnama](#): > > yyyyMMdd yes I tried both mapping as shown and its not working. trunc(to\_date(to\_char(cast(create\_date as date),'MM/DD/YYYY') ,'MM/DD/YYYY')) as createddt createddt": { "type": "date", "format": "yyyy-MM-dd||MM/dd/yyyy||strict\_year\_month\_day" } ---
**Author:** ![Rohithnama](https://avatars.discourse-cdn.com/v4/letter/r/919ad9/32.png) [@Rohithnama](https://discuss.elastic.co/u/Rohithnama)\ **Post date:** [September 18, 2017, 7:41pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892/6 "2017-09-18T19:41:09Z")
Could you update the error message that you are getting now? Btw, the date you are indexing should be of type string and this could be a possible issue. Please try the above and then send the error message, if you get any! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 16, 2017, 7:41pm UTC](https://discuss.elastic.co/t/date-format-parsing-exception/100892/7 "2017-10-16T19:41:57Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.