date format when using interval

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hajek@nspuh.cz - 24 May 2007 08:32 GMT

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

Reply to this Message

hajek@nspuh.cz - 24 May 2007 08:34 GMT

On May 24, 9:32 am, h...@nspuh.cz wrote:

> Hi,

INFORMIX-SQL Version 7.20.UE1, IDS 7.31, sorry.

> select
>
[quoted text clipped - 10 lines]
>
> Thanks, Michal

Reply to this Message

Jonathan Leffler - 24 May 2007 10:11 GMT

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.
[quoted text clipped - 9 lines]
>
> > 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.

Reply to this Message

hajek@nspuh.cz - 24 May 2007 10:21 GMT

On May 24, 11:11 am, Jonathan Leffler <jonathan.leff...@gmail.com>
wrote:

> On May 24, 12:34 am, h...@nspuh.cz wrote:
>
[quoted text clipped - 18 lines]
> 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

Reply to this Message