nls_date_format

Description

The nls_date_format variable controls the format for converting a date to a string and the format for implicitly converting a string to a date.

Privilege requirements

• Query variables

  • Global level

    Both the sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view (in Oracle-compatible mode) to query the values of global system variables.

  • Session level

    Both the sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (in Oracle-compatible mode) to query the values of session system variables.

• Modify variables

  • Global level

    • Tenants can modify global system variables directly.

    • Oracle-compatible tenants can modify global system variables only with the ALTER SYSTEM privilege.

  • Session level

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

Attributes

Considerations

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

Configuration examples

Set the format for 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

• Date and time expressions
• Considerations for data conversion