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

Question

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

Navigation

#1 by (3 votes)

4

I have the following problem:

I use JDBC to connect to Oracle. And I call rs.getTimestamp(index) to get a date field.

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

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

Note: this variation only occurs in getTimestamp of Java. If I run TO_DATE directly in the database, the result is always the same.

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

java oracle

asked by anonymous 14.03.2017 / 13:10

1 answer

Maintenance environment for ecomerce - PrestaShop Excel - Create QR Code with data of 4 cells

score 3 · Answer 1

As you said, TO_DATE() expects a string as a parameter. When you execute TO_DATE(SYSDATE, 'YYYY-MM-DD') implicitly Oracle converts your query to TO_DATE(TO_CHAR(sysdate), 'DD MONTH YYYY') and since the second parameter was not passed to TO_CHAR(sysdate) it will get the default mask of your NLS_SESSION_PARAMETERS. This way it may happen that some dates work with the default mask and some do not.