date+hour = datetime
Hi,
IFX 11.50 xC7
I have a table with this structure:
create table xyz ( date_a datehour_a datetime hour to second );
I need to group this two fields into a datetime year to second field.
Just sum the two fields, don't work.
I can do this with this code
hour_a::datetime year to second - (today - date_a) units day
This is very ugly code.. there is other way to do this ?
I'm looking into the manual and not figure out how to do...
I can use others way (converting to char and converting back), but is worse of
this...
Is this better?
select *,
((date_a::datetime year to day)::varchar(10) || ' ' ||hour_a::varchar(8))::datetime year to second
From xyz
OR
select *,cast(cast(cast(date_a as datetime year to day) as varchar(10)) || ' ' ||
cast(hour_a as varchar(8)) as datetime year to second)
From xyz
*Jairo Gubler*
//
Em 11/10/2010 08:46, Cesar Inacio Martins escreveu:
> create table xyz ( date_a date
> hour_a datetime hour to second )
Hi Jairo,
Honestly, I want something more "clear" .
This kind of code what me and you write is a "invite to error".
When someone read it, we hope they know how datetime cast works and there is
no effect over the locale defined and this kind of casts works only with
datetime, if try with date will get a differ behave... and all this is a big
code to do something to much simple...
This kind of resource I always missing on Informix... :(
Anyway... thanks for you message!
Regards
Cesar
ps.: Jairo, não sei se você sabe, mas agora temos o grupo brasileiro de
usuários informix - BRIUG (www.briug.org)
--- Em seg, 11/10/10, Jairo Gubler <jairo.gubler@digitro.com.br> escreveu:
De: Jairo Gubler <jairo.gubler@digitro.com.br>
Assunto: Re: date+hour = datetime [21612]
Para: ids@iiug.org
Data: Segunda-feira, 11 de Outubro de 2010, 10:47
Is this better?
select *,
((date_a::datetime year to day)::varchar(10) || ' ' ||hour_a::varchar(8))::datetime year to second
>From xyz
OR
select *,cast(cast(cast(date_a as datetime year to day) as varchar(10)) || ' ' ||
cast(hour_a as varchar(8)) as datetime year to second)
>From xyz
*Jairo Gubler*
//
Em 11/10/2010 08:46, Cesar Inacio Martins escreveu:
> create table xyz ( date_a date
> hour_a datetime hour to second )
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
I'd write a stored procedure to do the job - and use it.
Alternatively, you can use an expression like this:
select extend(date_a, year to second) + (hour_a - datetime(0:0:0) hour to
second) from xyz;
The stored procedure, therefore, is trivial:
CREATE PROCEDURE date_time(d DATE DEFAULT TODAY,
h DATETIME HOUR TO SECOND DEFAULT CURRENT YEAR TO SECOND)
RETURNING DATETIME YEAR TO SECOND AS dt;
RETURN EXTEND(d, YEAR TO SECOND) + (h - DATETIME(0:0:0) HOUR TO SECOND);
END PROCEDURE;
You could even change the type of the first argument to DATETIME YEAR TO
DAY; but you still need the EXTEND to 'add zero time' to the DATE value.
Now you write:
SELECT date_time(date_a, hour_a) FROM xyz;
On Mon, Oct 11, 2010 at 13:14, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:
> Honestly, I want something more "clear" .
> This kind of code what me and you write is a "invite to error".
>
> When someone read it, we hope they know how datetime cast works and there
> is
> no effect over the locale defined and this kind of casts works only with
> datetime, if try with date will get a differ behave... and all this is a
> big
> code to do something to much simple...
>
> This kind of resource I always missing on Informix... :(
> Anyway... thanks for you message!
>
> Regards
> Cesar
>
> ps.: Jairo, não sei se você sabe, mas agora temos o grupo brasileiro de
> usuários informix - BRIUG (www.briug.org)
>
> --- Em seg, 11/10/10, Jairo Gubler <jairo.gubler@digitro.com.br> escreveu:
>
> De: Jairo Gubler <jairo.gubler@digitro.com.br>
> Assunto: Re: date+hour = datetime [21612]
> Para: ids@iiug.org
> Data: Segunda-feira, 11 de Outubro de 2010, 10:47
>
> Is this better?
>
> select *,
> ((date_a::datetime year to day)::varchar(10) || ' ' ||> hour_a::varchar(8))::datetime year to second
> >From xyz
>
> OR
>
> select *,> cast(cast(cast(date_a as datetime year to day) as varchar(10)) || ' ' ||
> cast(hour_a as varchar(8)) as datetime year to second)
>
> >From xyz
>
> *Jairo Gubler*
> //
>
> Em 11/10/2010 08:46, Cesar Inacio Martins escreveu:
> > create table xyz ( date_a date
> > hour_a datetime hour to second )>
>
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
--20cf301d42b6e600c10492c6b2aa