TO_DATE(SYSDATE, 'YYYY-MM-DD') works sometimes

Question

TO_DATE(SYSDATE, 'YYYY-MM-DD') works sometimes

Asked 7 years, 3 months ago

Viewed 4,925 times

4

I have the following problem:

I use the JDBC to connect with Oracle. And I call rs.getTimestamp(index) to pick up a date field.

When I run the function TO_DATE(SYSDATE, 'YYYY-MM-DD') the getTimestamp sometimes returns a negative value, sometimes returns the correct value.

I know that the TO_DATE is not being used correctly as it should receive a string. But even so, I would like to know why the result varies.

Obs: this variation only occurs in getTimestamp java. If I run the TO_DATE directly in the bank, the result is always the same.

Note²: When the result is negative, the formatted date returned by the function is 0014-03-17 00:00:00.0 and when the right result comes as 2017-03-14 00:00:00.0.

Maybe this will help http://www.guj.com.br/t/formatar-data-no-padrao-dd-mm-yyyyyy-hh-mm-ss/42479

– R.Santos

2017/03/14 at 14:45
TRUNC(SYSDATE)? Thus, the time is taken.

– Marcelo Shiniti Uchimura

2018/06/30 at 18:12

1 answer

Browser other questions tagged java oracle

You are not signed in. Login or sign up in order to post.

by Lucas Duarte • **111** points · Answer 1 · 2017-03-14T12:56:42+00:00

Like you said, the TO_DATE() expects a string as parameter. When you perform TO_DATE(SYSDATE, 'YYYY-MM-DD') implicitly Oracle converts its query to TO_DATE(TO_CHAR(sysdate), 'DD MONTH YYYY') and as the second parameter was not passed to the TO_CHAR(sysdate) It will take the standard mask of your NLS_SESION_PARAMETERS. This way it can happen that some dates work with the standard mask and others do not.