[Error: ORA-01843: not a valid month]

[MrBayene]

1. Review the user manual

2. Describe the problem Using SQLdeveloper I can run this query without problem: update offer set startdate = '2019-10-01' where OFFER_ID =17160668

But when i try to do it with a js script I get a "[Error: ORA-01843: not a valid month]".

This is command I run:

await run("update offer set startdate = '2019-10-01' where OFFER_ID = 17160668");

where the run function is as follows:

async function run(query) {
    let connection;
    const connectionString =**********
    try {
        connection = await oracledb.getConnection({
            user: **********,
            password: **********,
            connectString: connectionString
        });
        console.log(query);
        let result = await connection.execute(query);
        return result;
    } catch (err) {
        console.error(err);
    } finally {
        if (connection) {
            try {
                await connection.close();
            } catch (err) {
                console.error(err);
            }
        }
    }
}

Worth mentioning is that I can other updates that don't involve date. and basically any other query. But somehow the formating of the date gives me that error.

DO you guys have any help?

BR /Ivan

[dmcghan]

The way the code is written, you're passing a string to a date field. Oracle is trying to do implicit data conversion using the default date format mask for your session. You can see the value with:

select value
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT'

That value does not match the format you're using in the string, hence the error.

Here are two options:

• Use a date literal. The format you're using happens to be the format used for date literals in Oracle Database. If you just place the DATE keyword before the string value, you'll get a valid date.
• Use TO_DATE. TO_DATE accepts a string and a format mask, so you match the two explicitly. to_date('2019-10-01', 'YYYY-MM-DD').

Both of those options are explicit. What you don't want to do is rely on implicit formats (e.g. use TO_DATE without a format mask, which implicitly uses NLS_DATE_FORMAT): to_date('01/10/2019'). This will break if someone changes the NLS_DATE_FORMAT value.

[cjbj]

There are a couple of other solutions that may have their place.

At the environment level you can do something like:

export NLS_LANG=.AL32UTF8
export NLS_DATE_FORMAT='YYYY-MM-DD'
node myapp.js

Note that if you don't set NLS_LANG, then NLS_DATE_FORMAT is not checked.

Alternatively you can run an ALTER statement after connecting:

await connection.execute(`alter session set nls_date_format = 'YYYY-MM-DD'`);

The problem with this is that it adds the costs of another statement execution. With long-lived connection pools, this can be reduced to a trivial overhead by using a sessionCallback, see Connection Tagging and Session State.

Check out https://oracle.github.io/node-oracledb/doc/api.html#datehandling and https://jsao.io/2016/09/working-with-dates-using-the-nodejs-driver/