A DATE is not the characters on the screen. SQL*Plus turns that value into text with the session's date mask, so one stored day prints as 15-JAN-2026 in one session and 2026-01-15 in another. The column is unchanged. Numbers follow the same split: the value uses a period in SQL, and the printed separators come from the session.
Oracle DBA Lesson 21E — Why Dates and Numbers Look Different
What is stored
Oracle stores a DATE as century, year, month, day, hour, minute, and second. There is no format string in the column and no time zone. A NUMBER stores the numeric value. Group separators and decimal characters are not part of that value.
When a client prints the column with no format, Oracle converts it with the session defaults: NLS_DATE_FORMAT for a DATE, NLS_TIMESTAMP_FORMAT for a TIMESTAMP, and NLS_NUMERIC_CHARACTERS for a number. ALTER SESSION changes that conversion for the connection that ran it. It does not update existing rows.
Two sessions that disagree on the text can still hold the same value. Confirm with an explicit mask, not with the default print:
SELECT TO_CHAR(order_ts, 'YYYY-MM-DD HH24:MI:SS') AS same_text
FROM orders
WHERE order_id = 1001;If that text matches in both sessions, the stored instant matches. If it does not, the values differ. A format change is the wrong explanation.
Read the session you are in
NLS_SESSION_PARAMETERS is the set of rules this connection uses. Every user can read their own rows.
SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN (
'NLS_TERRITORY',
'NLS_LANGUAGE',
'NLS_DATE_LANGUAGE',
'NLS_DATE_FORMAT',
'NLS_TIMESTAMP_FORMAT',
'NLS_TIMESTAMP_TZ_FORMAT',
'NLS_NUMERIC_CHARACTERS',
'NLS_CALENDAR')
ORDER BY parameter;NLS_DATE_FORMAT is the default mask for DATE display and for a text-to-date conversion that omits a format model. A common American default is DD-MON-RR. NLS_NUMERIC_CHARACTERS is two characters. The first is the decimal character. The second is the group character. ., means 1,234.5. ,. means 1.234,5.
NLS_DATABASE_PARAMETERS records the database defaults, including the character set chosen at creation. NLS_INSTANCE_PARAMETERS records the initialization parameters. Neither view is the conversion your statement just used. Read the session view after you connect.
Where the session values come from
NLS_LANG is a client environment variable with three parts:
NLS_LANG=AMERICAN_AMERICA.AL32UTF8AMERICANis the language. It sets message language and the default language for day and month names (NLS_DATE_LANGUAGE).AMERICAis the territory. It sets the default date mask, numeric characters, and currency.AL32UTF8is the client character encoding. It is not a date mask and it is not a decimal character.
OCI clients, including SQL*Plus, apply NLS_LANG at connect time. Other client NLS variables, including NLS_DATE_FORMAT and NLS_NUMERIC_CHARACTERS, override the territory defaults only when NLS_LANG itself is set. If NLS_LANG is unset, those extra variables are ignored and the session stays on the instance defaults.
SQL*Plus then runs glogin.sql from $ORACLE_HOME/sqlplus/admin and login.sql from the current directory or SQLPATH. An ALTER SESSION in either file replaces the environment. Two engineers with the same NLS_LANG still print different dates when one of them has a site profile.
SQL Developer applies the NLS settings in its preferences after the connection opens. The JDBC Thin driver does not read the shell variable NLS_LANG. In both cases the proof is the same query: NLS_SESSION_PARAMETERS from that program's session, not echo $NLS_LANG in a different terminal.
Pass a date without asking the session
An ANSI date literal is a DATE at midnight. The text must be YYYY-MM-DD. The session mask is not consulted.
SELECT TO_CHAR(DATE '2026-01-15', 'YYYY-MM-DD') AS shown_date
FROM dual;A quoted string is not a date literal. Oracle converts '2026-01-15' with NLS_DATE_FORMAT. On a session whose mask is DD-MON-RR that conversion fails with ORA-01861. On a session whose mask is YYYY-MM-DD it succeeds. The statement did not change. The session did.
Name the mask, and name the language when the mask contains a month word. JAN is not valid in every NLS_DATE_LANGUAGE.
SELECT TO_DATE('15-JAN-2026',
'DD-MON-YYYY',
'NLS_DATE_LANGUAGE=AMERICAN') AS d
FROM dual;A TIMESTAMP literal is also fixed: TIMESTAMP '2026-01-15 08:30:00'. Changing NLS_DATE_FORMAT does not change how a TIMESTAMP column prints. That display uses NLS_TIMESTAMP_FORMAT. A TIMESTAMP WITH TIME ZONE uses NLS_TIMESTAMP_TZ_FORMAT.
The time is still in the DATE
A mask of DD-MON-YYYY hides the clock. Two rows can both print 15-JAN-2026 and differ by hours. Print the time before you treat them as equal.
SELECT order_id,
TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') AS stored
FROM orders
WHERE order_date >= DATE '2026-01-15'
AND order_date < DATE '2026-01-16';That range keeps the index on order_date. TO_CHAR(order_date, 'YYYY-MM-DD') = '2026-01-15' does not use a plain index on the column, and it also drops the time from the comparison. A row at 15 January 18:00 matches the text predicate and the range predicate. A row at 16 January 00:00 matches neither. A row at 15 January 00:00:01 matches both. The failure mode is the row you never see because the default mask rounded your eyes to the day.
DATE '2026-01-15' is midnight. order_date = DATE '2026-01-15' misses every row later that day. Use the half-open range above when the column carries a time.
The RR element in the default American mask maps a two-digit year into a century window around the current year. YY uses the current century. Write four-digit years in scripts. DD-MON-YYYY does not depend on that window.
Numbers cross the same boundary
A SQL numeric literal uses a period and no group separator. 1234.5 is one value. 1,234 is not one number. The comma separates expressions, so this query returns two columns:
SELECT 1,234 FROM dual;On output, G is the group character and D is the decimal character from the session, unless the call supplies NLS_NUMERIC_CHARACTERS. FM removes the leading pad. This call prints 1.234,5 in every session:
SELECT TO_CHAR(1234.5,
'FM9G999D9',
'NLS_NUMERIC_CHARACTERS='',.''') AS shown_number
FROM dual;Incoming text goes the other way. Match the characters that are actually in the string. This returns the value 1234.5:
SELECT TO_NUMBER('1.234,5',
'9G999D9',
'NLS_NUMERIC_CHARACTERS='',.''') AS n
FROM dual;An implicit conversion of that same string uses the session separators. When the session decimal character is a period, Oracle raises ORA-01722. Inserts, binds that arrive as text, and CSV loads fail for that reason while a numeric literal of 1234.5 in the same statement succeeds.
Fix the script, not the laptop
Set the session at the top of a script that must print the same text on every host. Do not rely on the engineer's profile.
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
ALTER SESSION SET NLS_CALENDAR = 'GREGORIAN';That block changes the connection. It does not change stored dates or numbers. For a value that leaves the database inside application code, still call TO_CHAR or TO_NUMBER with the mask. A later ALTER SESSION in the same connection will not rewrite text you already formatted.
One adjacent case is not a format problem. TIMESTAMP WITH LOCAL TIME ZONE is converted to the session time zone on the way out. Two sessions can show different clock times for that type even with the same explicit mask. A plain DATE does not. If the explicit TO_CHAR matches, stop looking at NLS_DATE_FORMAT.
No comments:
Post a Comment