LightDB——设置会话级日期格式nls_date_format

当我们在使用date数据类型、to_date函数输出日期时，返回的是默认的日期格式。而当我们想要特定的日期格式方便我们后续使用，需要设置日期格式。

本文章介绍的是lightdb-x的Oracle模式下，对nls_date_format会话级参数的设置。

会话级日期格式支持的参数

NLS_DATE_FORMAT 是 LightDB-X数据库oracle模式中一个重要的参数，它决定了在数据库中如何显示日期。该参数控制了日期类型的默认显示格式，包括日期类型的输出和输入。以下是 NLS_DATE_FORMAT 的作用和目的：

1. 控制日期的显示格式： NLS_DATE_FORMAT 确定了在 SQL 查询结果中日期的显示格式。这样，当查询结果包含日期类型的列时，数据库会根据 NLS_DATE_FORMAT 参数的设置来格式化日期值。

2. 影响日期的输入格式： 除了影响查询结果的显示格式外，NLS_DATE_FORMAT 还影响了用户在 SQL 语句中输入日期值的格式。当用户向数据库中插入日期值时，或者在 SQL 查询中使用日期值作为条件时，数据库会根据 NLS_DATE_FORMAT 参数的设置来解析日期字符串。

3. 提高数据可读性： 通过设置适当的日期格式，可以增强查询结果的可读性，使日期值以一种符合用户习惯的格式显示，从而更易于理解和处理。

Element	Description
CC	Century.If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.For example, 2002 returns 21; 2000 returns 20.
D	Day of week (1-7). This element depends on the NLS territory of the session.
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DL	Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format 'fmDay, dd. Month yyyy'.Restriction: You can specify this format only with the TS element, separated by white space.
DY	Abbreviated name of day.
FF [1..9]	Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in DATE formats.Examples: 'HH:MI:SS.FF'``SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM	Returns a value with no leading or trailing blanks.See Also: FM
FX	Requires exact matching between the character data and the format model.See Also: FX
HH HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
IW	Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard.A calendar week starts on Monday.The first calendar week of the year includes January 4.The first calendar week of the year may include December 29, 30 and 31.The last calendar week of the year may include January 1, 2, and 3.
IYYY	4-digit year of the year containing the calendar week, as defined by the ISO 8601 standard.
IYY IY I	Last 3, 2, or 1 digit(s) of the year containing the calendar week, as defined by the ISO 8601 standard.
J	Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI	Minute (0-59).
MM	Month (01-12; January = 01).
MON	Abbreviated name of month.
MONTH	Name of month.
PM P.M.	Meridian indicator with or without periods.
Q	Quarter of year (1, 2, 3, 4; January - March = 1).
RM	Roman numeral month (I-XII; January = I).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
TS	Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD	Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH	Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZM	Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZR	Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.Example: US/Pacific
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
Y,YYY	Year with comma in this position.
YYYY	4-digit year; S prefixes BC dates with a minus sign.
YYY YY Y	Last 3, 2, or 1 digit(s) of year.

使用

设置会话级NLS_DATE_FORMAT的执行sql如下：

其中options是自定义的日期格式如：'yyyy-mm-dd hh24mi:ss';

Alter session set NLS_DATE_FORMAT to|= 'options'|"options";

例子如下：

alter session set NLS_DATE_FORMAT to "yyyymmdd hh24";
select sysdate;
alter session set NLS_DATE_FORMAT to "yyyy-mm-dd hh24";
select to_date('2024-01-20 10:30:00', 'YYYY-MM-DD HH24:MI:SS');

设置前后对比：