Display format for timestamp time in Oracle

Timestamp data types in Oracle Many people use very little, so even the simplest query returns the results will not be clear exactly what the time point.

For example:

2 July-January-08 12.04.35.877000 am

What time is it? 12:04 noon, that is wrong, in fact, the use of To_char function conversion to obtain the following results:

2008-01-27 00:04:35:877000

This time is 00:04 points in the wee hours, not 12:04 minutes at noon.

This problem occurs for the following reasons:

Example:

SELECT To_char (to_date (' 2008-01-29 00:05:10 ', ' yyyy-mm-dd hh24:mi:ss '), ' yyyy-mm-dd hh:mi:ss am ') from DUAL

First, a 00:05 minute conversion, according to the ' YYYY-MM-DD hh:mi:ss am ' format to convert, the result is:

2008-01-29 12:05:10 a.m.

This means that when Oracle converts a date into a string, if the hour conversion uses a 12 binary format, 00 o'clock in the morning is considered to be 12 o'clock in the morning, then 1 o'clock in the morning, 2, 3 ... In Oracle, 12 of the timing is not from 0-11, but from 1-12, so if it is 0 o'clock in the night, you can not remember 1 points, it can only be recorded as 12 points. (Do not know whether this is related to foreigners ' habits?) ）

Now let's look at the processing of timestamp in Oracle:

SELECT VALUE from nls_session_parameters WHERE PARAMETER = ' Nls_timestamp_format '

Returns the result Dd-mon-rr hh.mi. Ssxff am, as you can see, here by default, the format of the timestamp used is 12 hours in the binary.

The problem has found its roots here.

Workaround:

PL/SQL Developer reads the NLS_TIMESTAMP_FORMAT format set in the registry, so long as the Oracle environment variable is set in the registry (that is, where Oracle_home is set) Nls_timestamp _format format (that is, create such a string entry, then set its value for you to convert the required mask, I generally set to Yyyy-mm-dd HH24:MI:SS:FF6), and then turn off PL/SQL DEVELOPER, and then continue to login, The timestamp time we see in the future will automatically be converted into the format we need.

Display format for timestamp time in Oracle