date format when using interval

Posted in 2007

hajek@nspuh.cz — 24 May 2007 00:32:17 -0700 — source: Usenet: comp.databases.informix

Hi, select select (today-day(today)+1) - INTERVAL (2) month (2) TO month D1, today D2 from systables where tabid=100 returns d1 d2 2007-03-01 24.05.2007 I need d1 to be in d2 format, how to do it? DBDATE is set to DMY4. Thanks, Michal

↪ replying to hajek@nspuh.cz

hajek@nspuh.cz — 24 May 2007 00:34:24 -0700 — source: Usenet: comp.databases.informix

On May 24, 9:32 am, h...@nspuh.cz wrote: > Hi, INFORMIX-SQL Version 7.20.UE1, IDS 7.31, sorry. > select > > select (today-day(today)+1) - INTERVAL (2) month (2) TO month D1, > today D2 > from systables where tabid=100 > > returns > d1 d2 > > 2007-03-01 24.05.2007 > > I need d1 to be in d2 format, how to do it? DBDATE is set to DMY4. > > Thanks, Michal

↪ replying to hajek@nspuh.cz

Jonathan Leffler — 24 May 2007 02:11:33 -0700 — source: Usenet: comp.databases.informix

On May 24, 12:34 am, h...@nspuh.cz wrote: > On May 24, 9:32 am, h...@nspuh.cz wrote: > INFORMIX-SQL Version 7.20.UE1, IDS 7.31, sorry. > > > select (today-day(today)+1) - INTERVAL (2) month (2) TO month D1, > > today D2 > > from systables where tabid=100 > > > returns > > d1 d2 > > > 2007-03-01 24.05.2007 > > > I need d1 to be in d2 format, how to do it? DBDATE is set to DMY4. The simple answer to your question is by converting from DATETIME to DATE. SELECT DATE((TODAY - DAY(TODAY) + 1) - INTERVAL(2) MONTH TO MONTH) D1, TODAY D2 FROM "informix".SysTables WHERE Tabid = 1; Note that WHERE Tabid = 1 is safer than WHERE Tabid = 100; we are not likely to delete SysTables, but you might delete the first table you created in the database (with Tabid = 100) at any time. You said ISQL 7.20; you didn't indicate which server version. I tested on IDS 10.00.UC5; I don't think it is version dependent, though. The DATE() function has been around since time immemorial.

↪ replying to Jonathan Leffler

hajek@nspuh.cz — 24 May 2007 02:21:36 -0700 — source: Usenet: comp.databases.informix

On May 24, 11:11 am, Jonathan Leffler <jonathan.leff...@gmail.com> wrote: > On May 24, 12:34 am, h...@nspuh.cz wrote: > > > On May 24, 9:32 am, h...@nspuh.cz wrote: > > INFORMIX-SQL Version 7.20.UE1, IDS 7.31, sorry. > > > > select (today-day(today)+1) - INTERVAL (2) month (2) TO month D1, > > > today D2 > > > from systables where tabid=100 > > > > returns > > > d1 d2 > > > > 2007-03-01 24.05.2007 > > > > I need d1 to be in d2 format, how to do it? DBDATE is set to DMY4. > > The simple answer to your question is by converting from DATETIME to > DATE. > > SELECT DATE((TODAY - DAY(TODAY) + 1) - INTERVAL(2) MONTH TO MONTH) D1, > TODAY D2 > FROM "informix".SysTables WHERE Tabid = 1; Yes, great! :-) > > Note that WHERE Tabid = 1 is safer than WHERE Tabid = 100; we are not > likely to delete SysTables, but you might delete the first table you > created in the database (with Tabid = 100) at any time. True. Thank you! Michal