Re: NVL changing data type of a datetime?
jbesson reported NVL on a datetime returning odd precision on 9.40.UC7 even when the column was NULL, with GL_DATETIME unset. Claus Samuelsen and scottishpoet suggested GL_DATETIME. jbesson found setting it to a %Y-%m-%d %H:%M:%S format worked but changed other datetime output, so he planned to code around it and worried about customers moving to 9.40.UC7.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
jbesson wrote:
> Hi all,
>
> I'm seeing some unusual behaviour in IDS 9.40.UC7
>
> I've a table with a nullable datetime year to second column. For rows
> where it's null I want CURRENT so I use a simple NVL. Trouble is, the
> result of that seems different in 9.40.UC7
>
> create table tbl (time datetime year to second);>
> insert into tbl values ('2005-01-01 00:00:00');>
> select
> time,
> nvl(time,CURRENT),
> nvl(time,CURRENT year to second),
> nvl(time,extend('2005-01-01 00:00:00', year to fraction))
> from tbl;
>
> With IDS 9.40.UC7 that query gives me this:
>
> time 2005-01-01 00:00:00
> (expression) 2005-01-01 00:00:00.000
> (expression) 2005-01-01 00:00:00
> (expression) 2005-01-01 00:00:00.000
>
>
> Note the milliseconds in 2 of the columns (.000 because I've got
> USEOSTIME turned off).>
> With all other versions of IDS I have to hand (7.31.UD2R1, 7.31.UD9,
> 9.30.UC1, 9.40.FC5, 10.00.UC1) the milliseconds are not returned, i.e.
> the query gives me this:
>
> time 2005-01-01 00:00:00
> (expression) 2005-01-01 00:00:00
> (expression) 2005-01-01 00:00:00
> (expression) 2005-01-01 00:00:00
>
> It looks like IDS 9.40.UC7 is casting the NVL to the data type of the
> second NVL argument whereas other versions keep the column's own data
> type.
>
> Obviously, I could make sure I use CURRENT year to second, but there
> are a lot of other queries using plain CURRENT scattered around our
> code that could have problems with the millseconds.
>
> Does anyone have any ideas why IDS 9.40.UC7 is doing this and if
> there's a way to change it?
>
> Thanks,
> JC
>
Has GL_DATETIME environment variable been set?
What if column 'time' is null, do you get the correct (expected) value?
No, I've not set GL_DATETIME. If time is null I get exactly the same weird behaviour... On 9.40.UC7: time (expression) 2005-12-01 11:58:03.000 (expression) 2005-12-01 11:58:03 (expression) 2005-01-01 00:00:00.000 On other IDS versions: time (expression) 2005-12-01 11:58:08 (expression) 2005-12-01 11:58:08 (expression) 2005-01-01 00:00:00
can you try setting GL_DATETIME export GL_DATETIME="%iY-%m-%d %H:%M:%S" and see if this resolves the problem
Yup, that works. Trouble with using that is it dictates the format of any datetime that's selected, so something like extend(current,year to day) will have HH:MM:SS in it. That's bound to break our code somewhere and might cause more trouble than NVL's misbehaviour. It's easy enough for me to code around the problem, my real issue is that if a customer changes to IDS 9.40.UC7 then our software is likely to break. Fixing up all the code will be a nightmare. Maybe we should just tell them to avoid that version of Informix.