How would I select just the system date/time from an Informix
datebase.

For example, in Oracle it would be "select sysdate from dual". I
just need the Informix equivalent.

TIA

kstahl wrote:

> How would I select just the system date/time from an Informix datebase.
>
> For example, in Oracle it would be "select sysdate from dual". I just
> need the Informix equivalent.


The standard Informix idiom is:

SELECT TODAY FROM Systables WHERE Tabid = 1;

Alternatives include:

CREATE TABLE Dual
(
Number INTEGER NOT NULL CHECK(Number = 0) PRIMARY KEY
);
INSERT INTO Dual VALUES(0);
REVOKE ALL ON Dual FROM PUBLIC;
GRANT SELECT ON Dual TO PUBLIC;

SELECT TODAY FROM Dual;

If you want time as well as date, then:

SELECT CURENT FROM Dual;

And so the list goes on...

SELECT ... FROM "informix".Systables WHERE Tabid = 1

works because there is only one entry in systables with tabid = 1, and
that's the entry for systables. If that goes missing, you've got
bigger problems than that your select doesn't work any more.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote:
[...]
> If you want time as well as date, then:
>
> SELECT CURENT FROM Dual;

Oops - typo: CURRENT -- optionally qualified by something like YEAR TO
FRACTION(3), or MONTH TO MINUTE, or ...

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler wrote:
> Jonathan Leffler wrote:
> [...]
>
>> If you want time as well as date, then:
>>
>> SELECT CURENT FROM Dual;
>
>
> Oops - typo: CURRENT -- optionally qualified by something like YEAR TO
> FRACTION(3), or MONTH TO MINUTE, or ...
>

Ok, thanks. That just what I needed. I don't work with Informix
very often (mostly Oracle and MSSQL), but occasionally I have to
delve in and I don't have much documentation available to me.

kstahl wrote:
> Jonathan Leffler wrote:
> > Jonathan Leffler wrote:
> > [...]
> >
> >> If you want time as well as date, then:
> >>
> >> SELECT CURENT FROM Dual;
> >
> >
> > Oops - typo: CURRENT -- optionally qualified by something like YEAR TO
> > FRACTION(3), or MONTH TO MINUTE, or ...
> >
>
> Ok, thanks. That just what I needed. I don't work with Informix
> very often (mostly Oracle and MSSQL), but occasionally I have to
> delve in and I don't have much documentation available to me.


I tried to respond to you directly but there was an e-mail delivery failure
so I'm posting this here.

The following link will get you to the on-line IBM Informix documentation.
http://www-306.ibm.com/software/data...ary/lists.html

--
June Hunt

June C. Hunt wrote:
> I tried to respond to you directly but there was an e-mail delivery failure
> so I'm posting this here.
>
> The following link will get you to the on-line IBM Informix documentation.
> http://www-306.ibm.com/software/data...ary/lists.html
>
> --
> June Hunt
>
>
My email address is purposefully munged (in an obvious sort of
way if you look at it closely enough) to prevent abuse by spam
harvesters.

I had looked at the on-line documentation but it seemed to be
leading me in so many different directions that I figured I would
just come here and ask people who have experience.

Hi,

If you run the statement:
SELECT CURRENT from systables where tabid = 1 ;
it will returns the current date and time, as everybody said to you.

But, if the "fraction" of seconds allways becomes 0 , see your
onconfig file the parameter:
USEOSTIME
If it is 0 (zero), the IDS don't 'ask' to the operational system the
right time.
IF it is 1 every time when it is called, the IDS makes a 'call' to the
operational system to get it. Is because that the performance can goes
down.

Best regards .... R. Ferronato

kstahl <ktsahl@yahoo.com> wrote in message news:<OrWdnVhmVvCIuJLd4p2dnA@comcast.com>...
> How would I select just the system date/time from an Informix
> datebase.
>
> For example, in Oracle it would be "select sysdate from dual". I
> just need the Informix equivalent.
>
> TIA

Ferronato wrote:

> Hi,
>
> If you run the statement:
> SELECT CURRENT from systables where tabid = 1 ;
> it will returns the current date and time, as everybody said to you.
>
> But, if the "fraction" of seconds allways becomes 0 , see your
> onconfig file the parameter:
> USEOSTIME
> If it is 0 (zero), the IDS don't 'ask' to the operational system the
> right time.
> IF it is 1 every time when it is called, the IDS makes a 'call' to the
> operational system to get it. Is because that the performance can goes
> down.
>
> Best regards .... R. Ferronato
>
> kstahl <ktsahl@yahoo.com> wrote in message news:<OrWdnVhmVvCIuJLd4p2dnA@comcast.com>...
>
>>How would I select just the system date/time from an Informix
>>datebase.
>>
>>For example, in Oracle it would be "select sysdate from dual". I
>>just need the Informix equivalent.
>>
>>TIA

I only need accuracy to the second, so fractional portions of
seconds won't affect what I'm doing. Ultimately I'm really only
interested in detecting whether the system time in the database
is in standard time or DST.