The units keyword

Posted in 2005

John Bejarano — Tue, 17 May 2005 20:49:23 — source: IIUG Forums & Mailing Lists

Currently in the process of converting some SQL from SQL Server to Informix (yay!). In one place we're inserting the current time plus an offset. Before it was: getutcdate() + (?/86400.0) I was pretty sure that the following would fail and it did: current year to fraction(3) + (?/86400.0) However, I figured the workaround would be this, but it failed also: current year to fraction(3) + ? units second The last one fails with the error message: 1264: Extra characters at the end of a datetime or interval. There are no other characters after that last interval. There's very little about this on google. Is this a bug in Informix? If so, is there a workaround? I also tried the following to no avail: current year to fraction(3) + interval (?) second to second That fails with the error message: 1261: Too many digits in the first field of datetimeor interval. Plus, though I suspect not, is there any more standard way to express date expressions (i.e. from SQL-92), that I'm not aware of? Thanks for any help. Stay well, John Bejarano.

↪ replying to John Bejarano

John Bejarano — Tue, 17 May 2005 22:02:47 — source: IIUG Forums & Mailing Lists

Jeez, well it seems I faked myself out while testing this on a test table. Turns out that, current year to fraction(3) + ? units second does in fact work. I was trying to do this with a literal datetime instead of current. I'm not going mad, really. It just seems that way. :) Thanks for your help. Kind regards, John Bejarano. --- "ART KAGEL, BLOOMBERG/ 731 LEXIN" <KAGEL@bloomberg.net> wrote: > The second problem is easy to fix. Change it to: > > current year to fraction(3) + interval (?) second(6) > to second > > Art S. Kagel > ----- Original Message ----- > From: John Bejarano <jbejarano@sbcglobal.net> > At: 5/17 17:39 > > Currently in the process of converting some SQL from > SQL Server to Informix (yay!). In one place we're > inserting the current time plus an offset. Before > it > was: > > getutcdate() + (?/86400.0) > > I was pretty sure that the following would fail and > it > did: > > current year to fraction(3) + (?/86400.0) > > However, I figured the workaround would be this, but > it failed also: > > current year to fraction(3) + ? units second > > The last one fails with the error message: > > 1264: Extra characters at the end of a datetime or > interval.> > There are no other characters after that last > interval. There's very little about this on google. > Is this a bug in Informix? If so, is there a > workaround? > > I also tried the following to no avail: > > current year to fraction(3) + interval (?) second to > second > > That fails with the error message: > > 1261: Too many digits in the first field of datetime> or interval. > > Plus, though I suspect not, is there any more > standard > way to express date expressions (i.e. from SQL-92), > that I'm not aware of? Thanks for any help. > > Stay well, > > John Bejarano. > > > >

↪ replying to John Bejarano

Jonathan Leffler — Wed, 18 May 2005 17:16:22 — source: IIUG Forums & Mailing Lists

"John Bejarano " <jbejarano@sbcglobal.net> wrote on 05/17/2005 01:49:28 PM: > Currently in the process of converting some SQL from > SQL Server to Informix (yay!). In one place we're > inserting the current time plus an offset. Before it > was: > > getutcdate() + (?/86400.0) > > I was pretty sure that the following would fail and it did: > > current year to fraction(3) + (?/86400.0) > > However, I figured the workaround would be this, but > it failed also: > > current year to fraction(3) + ? units second > > The last one fails with the error message: > > 1264: Extra characters at the end of a datetime or > interval.> > There are no other characters after that last > interval. There's very little about this on google. > Is this a bug in Informix? If so, is there a > workaround? > > I also tried the following to no avail: > > current year to fraction(3) + interval (?) second to > second > > That fails with the error message: > > 1261: Too many digits in the first field of datetime> or interval. Odd that you didn't continue with interval(?) second to fraction(3)... If you're using question marks, these are fragments of embedded SQL, probably in ODBC or JDBC, possibly in ESQL/C - they're not in DB-Access code, anyway, since there's no way to supply the values for the place holders in DB-Access. The construct INTERVAL(...) SECOND TO FRACTION(3) is primarily used for literals - you are expected to type actual digits in it. It is not a conversion function. When I tried a version of the code in ESQL/C, I got error -201, syntax error, on preparing the statement: stmt = "select current year to fraction(3) +" " interval(?) second to fraction(3)" " from informix.systables where tabid = 1"; $ prepare p_xx from $stmt; (String concatenation in C is wonderful!) > Plus, though I suspect not, is there any more standard > way to express date expressions (i.e. from SQL-92), > that I'm not aware of? Thanks for any help. I have previously posted some C code (usable with ESQL/C) that converts intervals to decimals; you need the inverse operation (of converting decimals to intervals), preferably inside the DBMS. I can see how I'd do that in SPL - fiddly rather than impossible. ...then the brainstorm - using your original idea as a starting point: stmt = "select current year to fraction(3)," " current year to fraction(3) + ? units fraction(3)" " from informix.systables where tabid = 1"; With appropriate formatting, etc (see code in attachment), I used an decimal offset of 86403.14159 and got: Got c: 2005-05-18 16:46:14.011 Got c+d: 2005-05-19 16:46:17.152 (a) My server runs in TZ=UTC0 (offset 7 hours from PDT). (b) There are 86400 seconds in a day - it wasn't an accidental number. (c) I expected the server to complain about too many leading digits in the interval, but it didn't - wahoo! (d) I'm not sure whether that's truly the documented, expected behaviour, but it is certainly the desired behaviour. (e) I didn't experiment using the double value directly - it would almost certainly work. I did try - in sqlcmd (equivalent to DB-Access), the notation: SELECT CURRENT + (912311415133.1231/86400.0) UNITS FRACTION(3) FROM informix.systables WHERE tabid = 1; and this worked - and there's no obvious reason why you can't put a question mark in that expression either. I've not fully explored what happens with UNITS SECOND instead of FRACTION(3) - it might then work with integers instead of fractions (superficial testing suggests that integers are indeed used with UNITS SECOND). You tried: > current year to fraction(3) + (?/86400.0) Use: current year to fraction(3) + (?/86400.0) UNITS FRACTION(3) -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Information Management Division 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" --=_alternative 005E5A9C88257005_= Content-Type: text/html; charset="US-ASCII" <tt>"John Bejarano " <jbejarano@sbcglobal.net> wrote on 05/17/2005 01:49:28 PM: > Currently in the process of converting some SQL from > SQL Server to Informix (yay!).  In one place we're > inserting the current time plus an offset.  Before it > was: > > getutcdate() + (?/86400.0) > > I was pretty sure that the following would fail and it did: > > current year to fraction(3) + (?/86400.0) > > However, I figured the workaround would be this, but > it failed also: > > current year to fraction(3) + ? units second > > The last one fails with the error message: > > 1264: Extra characters at the end of a datetime or > interval. > > There are no other characters after that last > interval.  There's very little about this on google. > Is this a bug in Informix?  If so, is there a > workaround? > > I also tried the following to no avail: > > current year to fraction(3) + interval (?) second to > second > > That fails with the error message: > > 1261: Too many digits in the first field of datetime > or interval. </tt> <tt>Odd that you didn't continue with interval(?) second to fraction(3)...</tt> <tt>If you're using question marks, these are fragments of embedded SQL, probably in ODBC or JDBC, possibly in ESQL/C - they're not in DB-Access code, anyway, since there's no way to supply the values for the place holders in DB-Access.</tt> <tt>The construct INTERVAL(...) SECOND TO FRACTION(3) is primarily used for literals - you are expected to type actual digits in it.  It is not a conversion function.  When I tried a version of the code in ESQL/C, I got error -201, syntax error, on preparing the statement:</tt> <tt>stmt = "select current year to fraction(3) +"</tt> <tt>" interval(?) second to fraction(3)"</tt> <tt>" from informix.systables where tabid = 1";</tt> <tt>$ prepare p_xx from $stmt;</tt> <tt>(String concatenation in C is wonderful!)</tt> <tt> > Plus, though I suspect not, is there any more standard > way to express date expressions (i.e. from SQL-92), > that I'm not aware of?  Thanks for any help.</tt> <tt>I have previously posted some C code (usable with ESQL/C) that converts intervals to decimals; you need the inverse operation (of converting decimals to intervals), preferably inside the DBMS.</tt>@@

The units keyword

Related threads

Your privacy choices