# ORA-01861 : literal does not match format string

**URL:** <https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263>\
**Category:** Logstash\
**Created:** [August 14, 2018, 12:26am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263 "2018-08-14T00:26:17Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![seungtae](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seungtae/32/71413_2.png) [@seungtae](https://discuss.elastic.co/u/seungtae)\
**Post date:** [August 14, 2018, 12:26am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/1 "2018-08-14T00:26:17Z")

</div>

Hello everyone.

I want to convert string to date.

My config, I use jdbc output plugin.

```
jdbc {
                                        ...		
												
					statement => ["INSERT INTO TB_REN016_020 (GATH_DTM) VALUES(?)",
								   "standard_result[gath_dtm]"]		
								   
					driver_class => "oracle.jdbc.driver.OracleDriver"
				}

```

the error is like this :

> {:exception=\>java.sql.SQLDataException: ORA-01861: literal does not match format string

So, I tried,

```
statement => ["INSERT INTO TB_REN016_020 (GATH_DTM) VALUES(?)",
    								   "TO_DATE('standard_result[gath_dtm]', 'yyyyMMddhh24miss')"]

```

but, it doesn't effect. Is there someone with an idea??

---

<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:** [August 14, 2018, 12:40am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/2 "2018-08-14T00:40:51Z")

</div>

> [@seungtae](#):
>
> "standard\_result[gath\_dtm]"

Are you trying to reference a nested field here?

---

<div class="post-metadata">

**Author:** ![seungtae](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seungtae/32/71413_2.png) [@seungtae](https://discuss.elastic.co/u/seungtae)\
**Post date:** [August 14, 2018, 12:41am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/3 "2018-08-14T00:41:57Z")

</div>

Thank you for your replying @Badger.

What is a nested field?? How can I try to reference a nested field?

---

<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:** [August 14, 2018, 12:44am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/4 "2018-08-14T00:44:30Z")

</div>

```
"standard_result[gath_dtm]"

```

Is this referencing a field within the event you are sending to the jdbc output?

---

<div class="post-metadata">

**Author:** ![seungtae](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seungtae/32/71413_2.png) [@seungtae](https://discuss.elastic.co/u/seungtae)\
**Post date:** [August 14, 2018, 1:05am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/5 "2018-08-14T01:05:59Z")

</div>

Yes. That's right!

"standard\_result[gath\_dtm]" is made in ruby filter.

---

<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:** [August 14, 2018, 1:24am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/6 "2018-08-14T01:24:57Z")

</div>

Does

```
"[standard_result][gath_dtm]"

```

work?

---

<div class="post-metadata">

**Author:** ![seungtae](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seungtae/32/71413_2.png) [@seungtae](https://discuss.elastic.co/u/seungtae)\
**Post date:** [August 14, 2018, 1:28am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/7 "2018-08-14T01:28:49Z")

</div>

Yes. It works. But, the column GATH\_DTM type is date, standard\_result[gath\_dtm] type is string..

So, it gives me an error too. ORA-01861: literal does not match format string

---

<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:** [August 14, 2018, 1:31am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/8 "2018-08-14T01:31:34Z")

</div>

You have not explained why kind of db you are using. You might need to use a CAST in the SQL.

---

<div class="post-metadata">

**Author:** ![seungtae](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/seungtae/32/71413_2.png) [@seungtae](https://discuss.elastic.co/u/seungtae)\
**Post date:** [August 14, 2018, 1:35am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/9 "2018-08-14T01:35:59Z")

</div>

yeah.. I think so..

In UPDATE case, it works like this.

```
statement => [
UPDATE TB_MEAIN_SOLA SET FINAL_ACCE_DTM = TO_DATE(?, 'yyyyMMddhh24miss') WHERE CID = ?",
"[standard_result][gath_dtm]",
"[matchingCid][0][cid]
]

```

So, I want use TO\_DATE function in INSERT SQL like this.

```
statement => ["INSERT INTO TB_REN016_020 (GATH_DTM) VALUES(?)",
    								   "TO_DATE('standard_result[gath_dtm]', 'yyyyMMddhh24miss')"]

```

But it doesn't work.. 😰 and I use Oracle db

---

<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:** [September 11, 2018, 1:48am UTC](https://discuss.elastic.co/t/ora-01861-literal-does-not-match-format-string/144263/10 "2018-09-11T01:48:54Z")

</div>

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