# Unable to convert a column with Date and time format from string to date

**URL:** <https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235>\
**Category:** Logstash\
**Created:** [January 23, 2020, 12:19pm UTC](https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235 "2020-01-23T12:19:16Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![sama.sowjanya](https://avatars.discourse-cdn.com/v4/letter/s/c2a13f/32.png) [@sama.sowjanya](https://discuss.elastic.co/u/sama.sowjanya)\
**Post date:** [January 23, 2020, 12:19pm UTC](https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235/1 "2020-01-23T12:19:16Z")

</div>

Hi... my csv file contains closedDate with only date(e.g: 9/3/2019) and createdDate with date and time(e.g: 9/4/2019 06:40).  
I'm able to convert "closedDate" field to date format but "createdDate" field is remaining as string.

Below is the filter I used.

filter {  
csv {  
separator =\> ","  
autogenerate\_column\_names =\> true

# skip\_header =\> true

```
    skip_empty_columns => true
 

  columns => ["IncidentNumber","CreatedDate","Status","ClosedDate","Prods","State"]

```

}  
mutate {convert =\> ["Prods", "integer"]}  
date { match =\> ["ClosedDate", "MM/dd/yyyy"]  
target =\> "ClosedDate" }  
date { match =\> ["CreatedDate", "MM/dd/yyyy HH:mm:ss"]  
target =\> "CreatedDate" }

}

Kindly help me  
Thank you

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [January 23, 2020, 1:45pm UTC](https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235/2 "2020-01-23T13:45:13Z")

</div>

> [@sama.sowjanya](#):
>
> date { match =\> ["CreatedDate", "MM/dd/yyyy HH:mm:ss"]  
> target =\> "CreatedDate" }

Your example date does not include seconds, so remove the ":ss". The pattern in a date filter has to exactly match the entire field.

---

<div class="post-metadata">

**Author:** ![sama.sowjanya](https://avatars.discourse-cdn.com/v4/letter/s/c2a13f/32.png) [@sama.sowjanya](https://discuss.elastic.co/u/sama.sowjanya)\
**Post date:** [January 28, 2020, 7:32am UTC](https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235/3 "2020-01-28T07:32:12Z")

</div>

thank you Badger

It is working. But i have got another issue difference in time  
e.g: closed\_date : 09-05-2018  
output shows "sep 4 2018 @ 11:30:00.000"

created\_date : 09-05-2018 14:26  
o/p "sep 05 2018 @ 02:26:00.000"

Where is the issue ?

---

<div class="post-metadata">

**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\
**Post date:** [January 28, 2020, 3:32pm UTC](https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235/4 "2020-01-28T15:32:11Z")

</div>

You may need to use the timezone option on the date filter. elasticsearch always stores timestamps as UTC. You use the timezone option to tell logstash what the local timezone on the log file is. kibana will translate the UTC timestamps from elasticsearch into the browsers local timezone.

---

<div class="post-metadata">

**Author:** ![sama.sowjanya](https://avatars.discourse-cdn.com/v4/letter/s/c2a13f/32.png) [@sama.sowjanya](https://discuss.elastic.co/u/sama.sowjanya)\
**Post date:** [January 29, 2020, 6:46am UTC](https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235/5 "2020-01-29T06:46:43Z")

</div>

Thank you Badger

Will try with the date filter.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [February 26, 2020, 6:46am UTC](https://discuss.elastic.co/t/unable-to-convert-a-column-with-date-and-time-format-from-string-to-date/216235/6 "2020-02-26T06:46:46Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.