RE: How to convert a DATETIME to an integer?

You may use this syntax to cast DATE to INT: col_name::INT For instance: CREATE TABLE mytable(col1 DATE); INSERT INTO mytable VALUES('12/15/2000'); INSERT INTO mytable VALUES('12/17/2000'); INSERT INTO mytable VALUES('12/21/2000'); SELECT col1 date, col1::INT date_as_int FROM mytable WHERE col1::INT <= 36876; SELECT UNIQUE TODAY today, TODAY::INT today_as_int FROM mytable; Csomi -----Original Message----- From: mike@excite.com [mailto:mike@excite.com] Sent: Sunday, December 17, 2000 1:01 AM To: informix-list@iiug.org Subject: How to convert a DATETIME to an integer? This can't be that hard. I've looked all over, but I can't figure out how to do this simple task. How do I convert a DATETIME to an integer, for use in the WHERE clause of a SELECT statement? Basically, I want to do something like: SELECT * FROM table WHERE INTEGER(some_datetime) / 1234 > 5678; Actually, it is more complicated than that, but if I could get that far, I can do the rest. I want the integer to be number of seconds since some time in the past, like January 1, 1970 0:00. I don't really care what the date from the past is, as long as it always uses the same one. The "Informix Guide to SQL" says: "The database server stores the internal format of the DATE or DATETIME column as an integer." How do I get at that integer? Thanks, Mike mikemulvaney@excite.com

Hmm, this isn't working for me. Should this work for a DATETIME as well as a DATE? I don't have a DATE field to test it on. Also, I am using Informix 7.3. Should that matter? The error I get is -201: Syntax error. Thanks, Mike Earlier, Csom Gyula <Csom@interface.hu> wrote: > >You may use this syntax to cast DATE to INT: > > col_name::INT > >For instance: > >CREATE TABLE mytable(col1 DATE);> >INSERT INTO mytable VALUES('12/15/2000'); >INSERT INTO mytable VALUES('12/17/2000'); >INSERT INTO mytable VALUES('12/21/2000');> >SELECT col1 date, col1::INT date_as_int >FROM mytable >WHERE col1::INT <= 36876; >SELECT UNIQUE TODAY today, TODAY::INT today_as_int >FROM mytable;> >Csomi > >-----Original Message----- >From: mike@excite.com [mailto:mike@excite.com] >Sent: Sunday, December 17, 2000 1:01 AM >To: informix-list@iiug.org >Subject: How to convert a DATETIME to an integer? > > >This can't be that hard. I've looked all over, but I can't figure out >how to do this simple task. > >How do I convert a DATETIME to an integer, for use in the WHERE clause >of a SELECT statement? > >Basically, I want to do something like: > >SELECT * FROM table >WHERE INTEGER(some_datetime) / 1234 > 5678;> >Actually, it is more complicated than that, but if I could get that >far, I can do the rest. > >I want the integer to be number of seconds since some time in the >past, like January 1, 1970 0:00. I don't really care what the date >from the past is, as long as it always uses the same one. > >The "Informix Guide to SQL" says: "The database server stores the >internal format of the DATE or DATETIME column as an integer." > >How do I get at that integer? > >Thanks, >Mike >mikemulvaney@excite.com

Yes, I think your going to need a 9.X engine to do a cast like Csomi specifies. I have not had a need to go from DATE to INT, but have needed to go in the other direction. Here's what I did: CREATE PROCEDURE humantime(p_unixtime INT) RETURNING CHAR(255) ; DEFINE vhumantime CHAR(255); --SET DEBUG FILE TO "/tmp/trace.sp"; --TRACE ON ; LET vhumantime = "" ; -- We have to subtract 14400 (4 hours) to adjust for gmt -- The " VERSION" bit ensures that we get back exactly 1 row SELECT datetime(1970-01-01 00:00:00) YEAR TO SECOND + (p_unixtime - 14400) UNITS SECOND into vhumantime from systables where tabname = " VERSION" ; RETURN vhumantime ; --TRACE OFF ; END PROCEDURE Maybe this will help you get started on the opposite function. Allen W. Jantzen, DBA Ned Davis Research mike@excite.com wrote: > > Hmm, this isn't working for me. Should this work for a DATETIME as > well as a DATE? I don't have a DATE field to test it on. > > Also, I am using Informix 7.3. Should that matter? > > The error I get is -201: Syntax error. > > Thanks, > Mike > > Earlier, Csom Gyula <Csom@interface.hu> wrote: > > > >You may use this syntax to cast DATE to INT: > > > > col_name::INT > > > >For instance: > > > >CREATE TABLE mytable(col1 DATE);> > > >INSERT INTO mytable VALUES('12/15/2000'); > >INSERT INTO mytable VALUES('12/17/2000'); > >INSERT INTO mytable VALUES('12/21/2000');> > > >SELECT col1 date, col1::INT date_as_int > >FROM mytable > >WHERE col1::INT <= 36876; > >SELECT UNIQUE TODAY today, TODAY::INT today_as_int > >FROM mytable;> > > >Csomi > > > >-----Original Message----- > >From: mike@excite.com [mailto:mike@excite.com] > >Sent: Sunday, December 17, 2000 1:01 AM > >To: informix-list@iiug.org > >Subject: How to convert a DATETIME to an integer? > > > > > >This can't be that hard. I've looked all over, but I can't figure out > >how to do this simple task. > > > >How do I convert a DATETIME to an integer, for use in the WHERE clause > >of a SELECT statement? > > > >Basically, I want to do something like: > > > >SELECT * FROM table > >WHERE INTEGER(some_datetime) / 1234 > 5678;> > > >Actually, it is more complicated than that, but if I could get that > >far, I can do the rest. > > > >I want the integer to be number of seconds since some time in the > >past, like January 1, 1970 0:00. I don't really care what the date > >from the past is, as long as it always uses the same one. > > > >The "Informix Guide to SQL" says: "The database server stores the > >internal format of the DATE or DATETIME column as an integer." > > > >How do I get at that integer? > > > >Thanks, > >Mike > >mikemulvaney@excite.com