nls_date_format

Description

nls_date_format specifies the format for converting a DATE value to a string and for implicitly converting a string to a DATE value.

Privilege requirements

• Query variables

  • Global level

    sys tenants and all user tenants can execute the SHOW VARIABLES statement or query the SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE view (Oracle-compatible mode) to obtain the value of a global system variable.

  • Session level

    sys tenants and all user tenants can execute the SHOW VARIABLES statement or query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (Oracle-compatible mode) to obtain the value of a session system variable.

• Modify variables

  • Global level

    • sys tenants can modify the value of a global system variable.

    • Oracle user tenants must have the ALTER SYSTEM privilege to modify the value of a global system variable.

  • Session level

    sys tenants and all user tenants can modify the value of a session system variable.

Attributes

Usage

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

Examples

Set the format for converting a DATE value 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