Convert DATETIME YEAR TO FRACTION to INTEGER/DECIMAL value
If I would like to convert a DATETIME value like '2005-10-19 00:01:34.346' to a INTEGER or DECIMAL value like '12345' how would I go about? I suppose Informix also stores dates as INTEGER/DECIMAL value? Is there a FUNCTION available? Kind Regards David Reed sending to informix-list
David Reed wrote:
> If I would like to convert a DATETIME value like '2005-10-19
> 00:01:34.346' to a INTEGER or DECIMAL value like '12345' how would I go
> about?
You need to be a lot more precise specifying what you want.
create function dt_to_integer(x datetime year to fraction)
returns integer;define i integer;
if x = datetime(2005-10-19 00:01:34.346') year to fraction then
let i = 12345;
else
let i = null;
end if;
return i;
end function;
You probably didn't mean that - but what did you mean?
> I suppose Informix also stores dates as INTEGER/DECIMAL value?
Informix stores DATE as an INTEGER, yes; RTFM.
Informix stores DATETIME as a DECIMAL, yes; RTFM.
> Is there a FUNCTION available?
Lots of them - but probably not what you're looking for.
Internally, the DECIMAL value stored for your example value is:
20051019000123.346
On the client, you can obtain that from a DATETIME value quite easily:
#include "datetime.h"
dec_t get_raw_decimal_from_datetime(const dtime_t *dt)
{
return(dt->dt_dec);
}
Or, in more idiomatic style:
int get_raw_decimal_from_datetime(const dtime_t *dt, dec_t *rv)
{
*rv = dt->dt_dec;
return(0);
}
This would work in C or C++ - and hence with ODBC too. More succinctly,
you can simply reference dt->dt_dec, too, without using a function call,
even.
If you want to do that in pure SPL or SQL, you have to work a lot
harder, and so does the server. Or you code a function in C rather like
the ones I showed and build it into a 'datablade' and use that.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
\