date format when using interval
Michal needed a date computed with TODAY minus an INTERVAL MONTH to display in the same format as TODAY, with DBDATE set to DMY4, on ISQL 7.20 and IDS 7.31. Jonathan Leffler solved it by wrapping the expression in the DATE() function to convert DATETIME to DATE, and advised using tabid = 1 rather than 100 in systables. Michal confirmed it worked.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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
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.
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