CODEWITHSUNDEEP
sqlnetezza
cryocaustik
cryocaustik

Reputation: 479

NZSQL/Code - How to set NZSQL to NOT round to the nearest whole number

Everyone.

I am using some functions in NZSQL and am finding that NZSQL is rounding to the nearest whole number and was not sure if this is the design of the functions, or if the rounding could be disabled.

One of the functions that I am using is

TO_NUMBER(AGE(column_a),'000')

and it rounds to the nearest number, but I would like to have it leave it at a decimal number. Something like 12.42. Is this possible? Should I be using a different function? I have tried using '00.000) but it still rounds...

Thanks in advance!

Upvotes: 1

Views: 528

Answers (1)

ScottMcG
ScottMcG

Reputation: 3887

The AGE function returns an interval which may not behave as you'd expect/hope when paired up with TO_NUMBER's format templates. The shape of the templates has a particular meaning that is different than what you might intuit.

For example, here I have a format template that corresponds to NUMERIC(20,6)

SYSTEM.ADMIN(ADMIN)=> select age('01/01/1960'::date) , to_number(age('01/01/1960'::date),'99999999999999999999.999999');
                AGE                |      TO_NUMBER
-----------------------------------+---------------------
 54 years 11 mons 15 days 23:17:21 | 541115231721.000000
(1 row)

Here you can see the interval expressed as digits in the result of the TO_NUMBER. The first two digits represent the 54 years, the next two represent the 11 months, and the last two before the decimal point represent the 21 seconds. Note that there is no value past the decimal point, and this is expected (well, by the design if not by us).

If we take one 9 off the template to the right or the left of the decimal point we get a malformed response. Notice that the 48 seconds is truncated to just a 4.

SYSTEM.ADMIN(ADMIN)=> select age('01/01/1960'::date) , to_number(age('01/01/1960'::date),'9999999999999999999.999999'), to_number(age('01/01/1960'::date),'99999999999999999999.99999');
                AGE                |     TO_NUMBER      |     TO_NUMBER
-----------------------------------+--------------------+--------------------
 54 years 11 mons 15 days 23:27:48 | 54111523274.000000 | 54111523274.000000
(1 row)

The point there was just to highlight that the format of the TO_NUMBER template does something other than what you likely expect/want.

What you probably want instead (if I get the right gist from your comment) is something like this, which uses DATE_PART as a loose substitute for DATEDIFF:

SYSTEM.ADMIN(ADMIN)=> select date_part('day',now() - '01/01/1960'::date) / 365.242;
  ?COLUMN?
------------
 54.9580826
(1 row)

Upvotes: 1

Related Questions