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

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

  • Session level

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

• Modify variables

  • Global effect

    • Tenants can modify global system variables in the sys tenant and user tenants.

    • For V4.x, tenants require the ALTER SYSTEM privilege to modify global system variables, as of V4.2.0 in Oracle mode.

  • Session effect

    Tenants can directly modify session system variables in their own tenant, in both the sys tenant and user tenants.

Attribute description

Considerations

The data type of start_date is DATE. The system variable NLS_DATE_FORMAT specifies the format model for converting the start_date value to a text value, which is then passed to the SQL text. The date and time format model can be a string enclosed in double quotation marks.

Configuration examples

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

• Session effect

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

• Global effect

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

References

• Date and time expressions
• Considerations for data conversion