Forum Stats

  • 3,770,598 Users
  • 2,253,138 Discussions
  • 7,875,504 Comments

Discussions

How to input Date format for Oracle SQL query using TypeORM?

User_08AYV
User_08AYV Member Posts: 2 Green Ribbon

I'm using TypeORM to call a procedure in my Oracle database.

There are some inputs to this procedure, including string or number format. However, date format always gives me an error.

[Case 1]

let connection = await this._database.getConnectin();
let sqlStr: string = "CALL P_OP_MR_SMOOTH(:1,:2,to_date(:3,'YYYY-MM-DD HH24:MI:SS'),:4,:5,:6)";

const result = await connection.manager.query(sqlStr,
  ["ABCD12345678", "3", "2021-10-06 16:00:00", "ABCDEFG-123456",
  {dir: oracledb.BIND_OUT, type: oracledb.NUMBER}, {dir: oracledb.BIND_OUT, type: oracledb.STRING});
)

ORA-01858: a non-numeric character was found where a numeric was expected

[Case 2]

let connection = await this._database.getConnectin();
let sqlStr: string = "CALL P_OP_MR_SMOOTH(:1,:2,to_timestamp(:3,'YYYY-MM-DD\"T\"HH24:MI:SS.ff3\"Z\"'),:4,:5,:6)";

// "2021-10-06T16:00:00.000Z"
const result = await connection.manager.query(sqlStr,
  ["ABCD12345678", "3", new Date(), "ABCDEFG-123456",
  {dir: oracledb.BIND_OUT, type: oracledb.NUMBER}, {dir: oracledb.BIND_OUT, type: oracledb.STRING});
)

ORA-01847: day of month must be between 1 and last day of month

Could someone help me figure out how to correct it?