Below are two functions that can be used to extract the year from a date in Oracle Database.
Continue readingTag: how to
4 Ways to Change the Time Zone in Oracle
In Oracle Database, time zones can be set in various places. Below are four ways to change the time zone when using Oracle.
Continue readingHow to Return a List of Valid Time Zones in Oracle Database
Sometimes we need to specify a time zone when using datetime functions in Oracle.
But there are a lot of time zones out there. How do we remember them all? And how do we know our system supports a given time zone?
Fortunately, we can query the V$TIMEZONE_NAMES view to return a list of valid time zones.
How to Check the Date Format of your Oracle Session
When working with functions that return dates, Oracle Database returns these dates based on the value of the NLS_DATE_FORMAT parameter.
There is also an NLS_TIMESTAMP_FORMAT parameter and an NLS_TIMESTAMP_TZ_FORMAT parameter, both of which have a datetime format mask that can be specified separately.
All of these parameters have their default values derived from the NLS_TERRITORY parameter (which by default, is operating system-dependent).
How to Check the Values of the NLS Parameters in Oracle Database
In Oracle Database, the NLS (National Language Support) parameters determine the locale-specific behaviour on both the client and the server.
These parameters can be set in various places, such as at the database level, in an initialization parameter file, in environment variables, at the session level, and even within some functions.
You can check the value of the NLS parameters with the following views.
Continue readingMethods of Setting NLS Parameters and their Priorities (Oracle Database)
Oracle Database includes a bunch of NLS (National Language Support) parameters that determine the locale-specific behaviour on both the client and the server.
These NLS parameters can be set in various places, each of which has a predetermined precedence order in relation to the others. The following table outlines these methods, as well as their priority in relation to the others.
Continue readingHow to Change the Language for your Oracle Session
In Oracle Database, the NLS_LANGUAGEÂ initialization parameter specifies the default language of the database.
This language is used for messages, day and month names, symbols for AD, BC, a.m., and p.m., and the default sorting mechanism.
The NLS_LANGUAGEÂ parameter also determines the default values of the NLS_DATE_LANGUAGE and NLS_SORT parameters.
This article outlines how to change the value of the NLS_LANGUAGEÂ and NLS_DATE_LANGUAGEÂ parameters.
How to Return a List of Supported Languages in Oracle
When using Oracle Database, you can find out what languages are supported by querying the V$NLS_VALID_VALUESÂ view.
This view lists all valid values for NLS parameters, including supported languages.
Continue readingHow to Return a List of Supported Territories in Oracle
When using Oracle Database, you can find out what territories are supported by querying the V$NLS_VALID_VALUESÂ view.
This view lists all valid values for NLS parameters, including supported territories.
Continue readingHow to Change the Date Format in your Oracle Session
When working with functions that return dates, Oracle Database returns these dates based on the value of the NLS_DATE_FORMAT parameter.
There is also an NLS_TIMESTAMP_FORMAT parameter and an NLS_TIMESTAMP_TZ_FORMAT parameter, both of which have a datetime format mask that can be specified separately.
All of these parameters have their default values derived from the NLS_TERRITORY parameter (which by default, is operating system-dependent).
So, to change the date format for your session, you can either update the NLS_TERRITORY parameter, or explicitly update each parameter individually.
This article provides examples of checking and changing these values, and viewing the results.
Continue reading