CODEWITHSUNDEEP
sqloracle-databasedatesqlplus
ziggy
ziggy

Reputation: 15876

Oracle dates - SQL plus - Changing the date format used in INSERT statements

I am trying to run a script that has the following insert statement through sqlplus

INSERT INTO mytable
VALUES('423234','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('2343423','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('234234','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('234234','test',NULL,'05-DEC-2012 18:30:03.000000');
INSERT INTO mytable
VALUES('2342343','test',NULL,'05-DEC-2012 18:30:04.000000');

When i run the script i get the following error:

ERROR at line 2:
ORA-01849: hour must be between 1 and 12

I thought this was just an issue with the NLS_DATEFORMAT parameter and all i had to do was change it for the session and it would work. The problem is i cant get the correct format.

Here is what i tried but i get a date format not recognized error

alter session  set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

Isn't the above pattern correct for date '05-DEC-2012 18:30:04.000000'

By the way the script runs fine on SQLDeveloper.

Thanks

Upvotes: 2

Views: 17017

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

You were close, but you need the TIMESTAMP equivalent:

alter session set NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS.FF';

It would generally be better to not rely on the NLS settings and implicit conversions, and instead do an explicit conversion:

INSERT INTO mytable
VALUES('2342343','test',NULL,
  TO_TIMESTAMP('05-DEC-2012 18:30:04.000000', 'DD-MON-YYYY HH24:MI:SS.FF');

It's better to explicitly list the columns too, to allow for future changes and to make it easier to check you have the values in the right order.

Upvotes: 5

Related Questions