nls_date_format

Description

nls_date_format specifies the format of converting a date to a string and the format of implicitly converting a string to a date.

Permission requirements

• Query variables

  • Global level

    Both the sys tenant and all user tenants can use the SHOW VARIABLES statement or the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view (Oracle mode) to view the values of Global system variables.

  • Session level

    Both the sys tenant and all user tenants can use the SHOW VARIABLES statement or the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (Oracle mode) to view the values of Session system variables.

• Modify variables

  • Global level

    • The sys tenant can directly modify the values of Global system variables.

    • For V4.x versions, starting from V4.2.0, Oracle user tenants require the ALTER SYSTEM privilege to modify the values of Global system variables.

  • Session level

    Both the sys tenant and all user tenants can directly modify the values of Session system variables of their own tenant.

Attributes

Usage notes

The data type of start_date is DATE. The value of start_date is converted to text using the format model specified in the system variable NLS_DATE_FORMAT, and then the result is passed to the SQL text. The datetime format model can simply consist of text enclosed in double quotes.

Configuration examples

Set the format of converting a date to a string to DD-MON-RR.

• Session level

  obclient> SET nls_date_format='DD-MON-RR';
  

• Global level

  obclient> SET GLOBAL nls_date_format='DD-MON-RR';
  

References

• Datetime expressions

• Security considerations for data conversion