Getting the Error: ORA-01843: not a valid month] error and don't know why

Question

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");

And the run function that it calls

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 formatting of the date gives me that error.

Any ideas what is happening?

/Ivan

Answer 1

The JS client has a different date format mask from the one you use in SQL Developer. So SQL Developer can correctly interpret '2019-10-01' and apply the implicit data conversion from string to date as to_date('2019-10-01','yyyy-mm-dd'). But the Javascript thinks dates have a format of (say) 'mm-dd-yyyy' and hurls because 2019 is not a valid month.

The solution is simple: don't rely on implicit data type conversions. It's just bad practice. Always pass dates using a date literal - date '2019-10-01' - and you'll never have this problem again.

Or you can use the Oracle to_date() function and pass a date format mask, but frankly the date literal is simpler and clearer.