CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Oto Shavadze
Oto Shavadze

Reputation: 42753

Timestamp data type in sql developer

In SQL Developer, I have field which type is timestamp(6)

When I am trying insert in this field data like this: 2013-07-10 12:22:22, SQL Developer shows error: not a valid month.

I have this settings in sql developer: enter image description here

Can you tell me please why happened this error?

Upvotes: 0

Views: 9058

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

Because your NLS_TIMESTAMP_FORMAT has an error; you have the MM element twice. It should be:

YYYY-MM-DD HH24:MI:SS

If you only use HH for the hour element you won't be able to distinguish between morning and afternoon times; HH24 will give you 01 for 1 AM and 13 for 1 PM, for example. The minute element is MI, not MM. At the moment it's trying to interpret 22 as a month number, which is invalid, as the error says.

But why use a timestamp over a date if you don't want the fractional seconds?

You should not rely on NLS settings anyway, generally. It would be much better, in anything other than ad hoc queries, to use to_timestamp('2013-07-10 12:22:22', 'YYYY-MM-DD HH24:MI:SS') to avoid any ambiguity and surprises when someone else runs your code with different NLS settings.

Upvotes: 4

Related Questions