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

Asked

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

  • TRUNC(SYSDATE)? Thus, the time is taken.

1 answer

3

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.

  • I am aware of this. But the oracle NLS conversion mask is one. I want to know why it works SOMETIMES. What is the default for this? This is a database, driver or java issue?

Browser other questions tagged

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