Find Out Which Currency Symbol your Session Uses in Oracle

Posted on by Ian

In Oracle Database, the following NLS parameters can be used to determine how currencies are displayed for the current session:

  • NLS_CURRENCY specifies the string to use as the local currency symbol for the L number format element.
  • NLS_ISO_CURRENCY determines what to use for the C format element.
  • NLS_DUAL_CURRENCY specifies what to use for the U format element.

The default value for these is determined by the NLS_TERRITORY parameter.

The L, C, and U number format elements can be used in functions like TO_CHAR() to format a number as a currency.

Continue reading →

How to Spell Out the Year when Formatting a Date in Oracle

Posted on by Ian

Oracle Database includes a good selection of datetime format elements. that enable us to format dates and times when using the TO_CHAR(datetime) function.

One of these (the YEAR format element) allows us to format a date with the year spelled out. For example, instead of 1972, we’d get NINETEEN SEVENTY-TWO.

We can also use SYEAR so that BC dates are prefixed with a minus sign (-).

Continue reading →

Fix Msg 529 “Explicit conversion from data type int to xml is not allowed” in SQL Server

Posted on by Ian

If you’re getting SQL Server error Msg 529 that reads something like Explicit conversion from data type int to xml is not allowed, it’s probably because you’re trying to perform a data type conversion that’s not permitted.

SQL Server doesn’t allow certain conversions. If you try to perform such a conversion, you’ll get this error.

Continue reading →