Re: NVL changing data type of a datetime?

jbesson wrote: 
The 9.40 manual has exactly the same explanation of NVL, which is
pretty vague, but I agree that the new 9.40 UC7 behaviour seems "more
correct".

I've played some more with datetimes and also char columns and it seems
to me that the "old" behaviour of NVL with datetimes is anomolous.

With chars, the data type returned is that of the longest argument,
i.e. NVLing a char(3) and a char(5) always gives you a char(5) value -
no matter which way round the arguments are and no matter which is null
or not. This is consistent with all versions of Informix I've tried.

On the other hand, with datetimes (for all versions I've tried apart
from 9.40.UC7), the data type returned is that of the first argument.
IDS 9.40.UC7 behaves the same way as it does for chars, i.e. it takes
the higher precision data type.

Although the 9.40 UC7 way seems better, it does mess up our code. I'm
trying to get my hands on an installation of the latest 10.0 server to
see if that has the new or old behaviour.

If, as I would suspect, the Informix team deliberately fixed the behaviour of NVL in 9.40.UC7, we almost certainly either already did or very shortly will put the same fix into IDS 10.00. it would be sensible to assume that the writing is on the wall - and fix your code to use CURRENT YEAR TO SECOND instead of just CURRENT. You might postpone the pain - mostly by coincidence - but that's all.

--
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler@xxxxxxxxxxxxx, jleffler@xxxxxxxxxx
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
.

Relevant Pages

  • Re: NVL changing data type of a datetime?
    ... I've played some more with datetimes and also char columns and it seems ... no matter which way round the arguments are and no matter which is null ... the data type returned is that of the first argument. ... IDS 9.40.UC7 behaves the same way as it does for chars, ...
    (comp.databases.informix)
  • Re: Perfromance issue
    ... This is not necessary for CHARs because ... this is just an artifact of the data type. ... Also, depending on the ANSI_PADDING settings, both VARCHARs and CHARs will ... To support what Andrew advised, the determination of either VARCHAR or CHAR ...
    (microsoft.public.sqlserver.clustering)
  • Re: Table fields of type MEMO are chunked cause of DISTINCT
    ... Contents in table fields of data type "memo" are chunked to a length of max ... 255 chars, if the fields are delivered by a query having the key word ... WHERE BUGS CAN BE REPORTED TO MICROSOFT? ...
    (microsoft.public.access.queries)
  • Re: Spaces consume room?
    ... This is a matter of data type in the table. ... the field is filled with trailing spaces to the size. ... > I have an ISA server logging to a SQL server. ... > get to 255 chars. ...
    (microsoft.public.sqlserver.programming)
  • Outlook still wraps text mails breaking URLs even after Office SP3
    ... real pain when it breaks URLs across two lines ... However it still breaks URLs at 72 chars if I set it to 132 chars ... Any ideas when MS is going to fix this rather basic but very annoying ...
    (microsoft.public.outlook.general)