Not a valid Month Error while Inserting timestamp value '2021-08-31T16:30:01.850'-CodePudding

Getting error while inserting timestamp value '2021-08-31T16:30:01.850'

INSERT INTO tbl_Account_Master (Account_ID, Verified_Date) 
VALUES (1, '2021-08-31T16:30:01.850')

Table definition :

CREATE TABLE tbl_Account_Master 
(
    Account_ID Number(10) NOT NULL,
    Verified_Date Timestamp(3) NULL 
)

Error:

ORA-01843: not a valid month

Thanks

CodePudding user response：

Don't use strings when dealing with dates and timestamps.

'2021-08-31T16:30:01.850'

is a string. It contains a timestamp format that I remember I have seen in SQL Server.

The following is a valid timestamp literal, both in standard SQL and Oracle:

timestamp '2021-08-31 16:30:01.850'

Hence:

INSERT INTO tbl_Account_Master (Account_ID, Verified_Date) 
VALUES (1, TIMESTAMP '2021-08-31 16:30:01.850')

CodePudding user response：

I have data script with these values only '2021-08-31T16:30:01.850'

Use TO_TIMESTAMP to explicitly convert from a string to a TIMESTAMP data type:

INSERT INTO tbl_Account_Master (Account_ID, Verified_Date) 
VALUES (1, TO_TIMESTAMP('2021-08-31T16:30:01.850', 'YYYY-MM-DD"T"HH24:MI:SS.FF3'));

You can also use implicit conversion (but this is not best practice) and alter the NLS_TIMESTAMP_FORMAT session parameter that Oracle will implicitly use as the format model for conversion from a string to a TIMESTAMP (when no explicit format model is given).

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS.FF3';

or, if you want to leave the precision of the fractional seconds ambiguous:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS.FF';

Then your INSERT query would work (at least until the NLS_TIMESTAMP_FORMAT session parameter was changed to something else and then your query would break again ... which is why it is not best practice to rely on implicitly setting the conversion format).

db<>fiddle here