nls_timestamp_format

Description

The nls_timestamp_format variable controls the format for converting timestamps or timestamps with time zones to strings, and for implicitly converting strings to timestamps or timestamps with time zones.

Privilege requirements

• Query variables

  • Global level

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

  • Session level

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

• Modify variables

  • Set the variable at the global level

    • Users in the sys tenant can directly modify the value of a global system variable.

    • Users in an Oracle user tenant must have the ALTER SYSTEM privilege to modify the value of a global system variable.

  • Set the variable at the session level

    Users in the sys tenant and all user tenants can directly modify the value of a session system variable in their own tenant.

Attributes

Considerations

The variable specifies the default date format to be used by the TO_CHAR and TO_TIMESTAMP functions. The default value is YYYY-MM-DD HH24:MI:SS.FF.

Examples

• Set the variable at the session level

  obclient> SET nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';
  

• Set the variable at the global level

  obclient> SET GLOBAL nls_timestamp_format='YYYY-MM-DD HH24:MI:SS.FF';
  

References

• to_CHAR
• to_TIMESTAMP
• Date and time expressions
• Considerations for data conversion