# Unable to change format of sql\_last\_value

**URL:** <https://discuss.elastic.co/t/unable-to-change-format-of-sql-last-value/101151>\
**Category:** Logstash\
**Created:** [September 20, 2017, 10:46am UTC](https://discuss.elastic.co/t/unable-to-change-format-of-sql-last-value/101151 "2017-09-20T10:46:49Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![kanchan](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kanchan](https://discuss.elastic.co/u/kanchan)\
**Post date:** [September 20, 2017, 10:46am UTC](https://discuss.elastic.co/t/unable-to-change-format-of-sql-last-value/101151/1 "2017-09-20T10:46:49Z")

</div>

Hi,  
I am using sql\_last\_value for incremented record using jdbc input plugin.but it is giving value like:

TIMESTAMP '2017-09-13 12:44:27.266000 +00:00'

I wanna remove Timestamp from this how can i do this please suggest proper solution.

code which i used:

input {  
jdbc {  
jdbc\_validate\_connection =\> true  
jdbc\_connection\_string =\> "jdbc:oracle:thin:@MUMCHORA66.ad.crisil.com:1821/COA"  
jdbc\_user =\> "test"  
jdbc\_password =\> "test"  
jdbc\_driver\_library =\> "/opt/logstash-5.5.0/ojdbc7.jar"  
jdbc\_driver\_class =\> "Java::oracle.jdbc.driver.OracleDriver"  
schedule =\> "\* \* \* \* \*"

```
    statement => "SELECT chd.RECORD_TYPE,chd.COALITION_ID,chd.COALITION_NAME,chd.BANK_ID,chd.TIMEPERIOD_ID,chd.BANK_CLIENT_ENTITY_NAME,chd.BANK_CLIENT_ENTITY_ID,

```

chd.BANK\_CLIENT\_ENTITY\_COUNTRY,chd.BANK\_CLIENT\_ENTITY\_REGION,chd.REGION\_ID,chd.PRODUCT\_ID,  
chd.BANK\_CLIENT\_ENTITY\_SECTOR,chd.PARENT1\_ENTITY\_NAME,chd.PARENT1\_CLIENT\_ENTITY\_ID,chd.PARENT1\_ENTITY\_COUNTRY  
,chd.PARENT1\_ENTITY\_REGION,chd.PARENT1\_ENTITY\_SECTOR,chd.PARENT2\_ENTITY\_NAME,chd.PARENT2\_CLIENT\_ENTITY\_ID,chd.PARENT2\_ENTITY\_COUNTRY,chd.PARENT2\_ENTITY\_REGION  
,chd.PARENT2\_ENTITY\_SECTOR,chd.PARENT3\_ENTITY\_NAME,chd.PARENT3\_CLIENT\_ENTITY\_ID,chd.PARENT3\_ENTITY\_COUNTRY,chd.PARENT3\_ENTITY\_REGION,chd.PARENT3\_ENTITY\_SECTOR  
,chd.PARENT4\_ENTITY\_NAME,chd.PARENT4\_CLIENT\_ENTITY\_ID,chd.PARENT4\_ENTITY\_COUNTRY,chd.PARENT4\_ENTITY\_REGION,chd.PARENT4\_ENTITY\_SECTOR,chd.PARENT5\_ENTITY\_NAME  
,chd.PARENT5\_CLIENT\_ENTITY\_ID,chd.PARENT5\_ENTITY\_COUNTRY,chd.PARENT5\_ENTITY\_REGION,chd.PARENT5\_ENTITY\_SECTOR,chd.CIQ\_NAME,chd.CIQ\_ID,chd.CIQ\_STATUS  
,chd.ULTIMATE\_PARENT,ULTIMATE\_ID,chd.HDID,[chd.ID](http://chd.ID),chd.cbcdid,chd.LISTSOURCE,chd.COMPANYCOUNTRY,chd.ULTIMATEPERCENT,chd.RA\_COMMENTS,chd.RA\_SOURCE,chd.QC\_COMMENTS,chd.QC\_SOURCE,  
(select cd.client from clients\_dim cd where chd.bank\_id=cd.client\_id) as BANK\_NAME,  
(select [ts.name](http://ts.name) from timeperiod\_syn ts where ts.id=chd.TIMEPERIOD\_ID ) as PERIOD\_NAME,  
chd.USER\_CONFIRMATION as USER\_CONFIRMATION,chd.USER\_VERIFIED\_DATE as USER\_VERIFIED\_DATE ,  
to\_date(TO\_CHAR(  
case when chd.user\_verified\_date is not null then chd.user\_verified\_date  
when chd.modified\_date is not null then chd.modified\_date  
when chd.insert\_date is not null then chd.insert\_date end,'DD-MON-YYYY hh:mi:ss'),'DD-MON-YYYY hh:mi:ss') as Date\_Status  
FROM CEM\_HISTORICAL\_METADATA chd  
where chd.active= 1 and NVL(modified\_date, insert\_date) \>  
to\_timestamp(REPLACE(REPLACE(REPLACE(:sql\_last\_value, 'TIMESTAMP ''', ''), '+00:00', ''),'''',''),'yyyy-Mm-DD HH24:MI:SS.FF')" }  
}

output {

```
elasticsearch {
	hosts => "test"
index => "hist_latest_5.5.0"        
}

```

}

---

<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:** [October 18, 2017, 10:47am UTC](https://discuss.elastic.co/t/unable-to-change-format-of-sql-last-value/101151/2 "2017-10-18T10:47:02Z")

</div>

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