Re: Datetime / date Converstion

----- Original Message ----- From: george <georgem@its.soft.net> To: <the_big_bang@my-deja.com> Cc: Informix-List (E-mail) <informix-list@iiug.org> Sent: Wednesday, September 29, 1999 11:16 AM Subject: Re: Datetime / date Converstion > ----- Original Message ----- > From: <the_big_bang@my-deja.com> > To: <informix-list@iiug.org> > Sent: Wednesday, September 29, 1999 7:16 AM > Subject: Datetime / date Converstion > > > > Hi, > > > > If a database field is datetime ( year to second ) and the query is > > > > where column < '1999-12-01' > > > > The query fails with error > > > > 1262: Non-numeric character in datetime or interval.> > > > The query is required in full qualified format like > > > > where column < datetime( 1999-12-01 ) year to day. > > I guess you have to use the EXTEND keyword. In your case, you could use > something like: > > where column < EXTEND('1999-12-01') YEAR TO SECOND Sorry, it's EXTEND('1999-12-01', YEAR TO SECOND) > Initially '1999-12-01' is a string, on performing the EXTEND it becomes a > datetime value 1999-12-01 00:00:00. > > > The problem is I have no control over the query generation and I need > > to succcessfully run the first query somehow. Any hints how it can be > > done ? Is there any env. variables etc. which can extend a value with > > defaults e.g. if some fields are not specified ? > > Well, if your Query is being constructed dynamically at runtime, then you > have to check if any of the values being compared aganist is of type > datetime, and then provide an EXTEND statement as above to convert the > string to a datetime. > > > Thanks in advance, >

Thanks for the reply, but it does not solves the problem As mentioned below, I have *** NO CONTROL *** over the client query and can't modify it in any way. I can control environment variable settings on client ( if they can help ). I am in full control of server side. Given these limitations, how to I make the query running ? Thanks, In article <7sseao$jml$1@news.xmission.com>, "george" <georgem@its.soft.net> wrote: > > > ----- Original Message ----- > From: george <georgem@its.soft.net> > To: <the_big_bang@my-deja.com> > Cc: Informix-List (E-mail) <informix-list@iiug.org> > Sent: Wednesday, September 29, 1999 11:16 AM > Subject: Re: Datetime / date Converstion > > > ----- Original Message ----- > > From: <the_big_bang@my-deja.com> > > To: <informix-list@iiug.org> > > Sent: Wednesday, September 29, 1999 7:16 AM > > Subject: Datetime / date Converstion > > > > > > > Hi, > > > > > > If a database field is datetime ( year to second ) and the query is > > > > > > where column < '1999-12-01' > > > > > > The query fails with error > > > > > > 1262: Non-numeric character in datetime or interval.> > > > > > The query is required in full qualified format like > > > > > > where column < datetime( 1999-12-01 ) year to day. > > > > I guess you have to use the EXTEND keyword. In your case, you could use > > something like: > > > > where column < EXTEND('1999-12-01') YEAR TO SECOND > > Sorry, it's EXTEND('1999-12-01', YEAR TO SECOND) > > > Initially '1999-12-01' is a string, on performing the EXTEND it becomes a > > datetime value 1999-12-01 00:00:00. > > > > > The problem is I have no control over the query generation and I need > > > to succcessfully run the first query somehow. Any hints how it can be > > > done ? Is there any env. variables etc. which can extend a value with > > > defaults e.g. if some fields are not specified ? > > > > Well, if your Query is being constructed dynamically at runtime, then you > > have to check if any of the values being compared aganist is of type > > datetime, and then provide an EXTEND statement as above to convert the > > string to a datetime. > > > > > Thanks in advance, > > > > Sent via Deja.com http://www.deja.com/ Before you buy.

> Initially '1999-12-01' is a string, on performing the EXTEND it > becomes a > datetime value 1999-12-01 00:00:00. > The problem is I have no control over the query generation and I > need to succcessfully run the first query somehow. Any hints how it > can be done ? Is there any env. variables etc. which can extend a value > with defaults e.g. if some fields are not specified ? Hi there, I do not know any environment variable to set up solving your problem. What came to my mind is: - rename the used table to another name - create a view on that renamed table using only the datetime-part your client is querying for Just a vague guess, but maybe a possible way... Bye, Chris