Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    61

    Unanswered: DataTime Calcule in SQL

    Hello..

    I'd like to return that query in Hours.. but it return in days...

    select dat_fim_ocorrencia - dat_ini_ocorrencia from movapo mov
    where mov.cod_empresa = '20' and
    mov.dat_refer >= '01/01/2008' and
    mov.dat_refer <= '01/01/2008'

    It's possible ?

  2. #2
    Join Date
    Oct 2007
    Posts
    61
    Well,
    i'm getting

    12202010 0 00:11
    12202010 0 00:08

    I'd like to convert 0, 00:11 to hours.

    How I do this ?

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    where you get this: "12202010 0 00:11" ???
    This not appear datetime or interval data type...
    do you mean this" 0 00:11" as result?

    what data type is dat_fim_ocorrencia and dat_inicio_ocorrencia?
    if your result of the calc over the fields is "0 00:11" , to convert to date, the only way what I know, unfortunately there is no direct way.. you need to convert to char and then convert to datetime.
    Code:
    create temp table tmp1( dt1 datetime year to second, dt2 datetime year to second);
    
    insert into tmp1 values ('2008-12-01 13:01:56', '2008-12-15 09:40:24') ;
    
    select dt1, dt2, dt1-dt2, dt2-dt1 from tmp1 ;
    
    select (dt2-dt1)::char(20), substr((dt2-dt1)::char(20), 11,5)::datetime hour to minute from tmp1;
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311

    my mistake.. sorry..

    maybe this can help you, but the datatype returned is a interval, to convert to datetime, need to convert to char an than to datetime hour to minute.
    But, be careful, because like this example, will return more than 24 hours what obvious is invalid to datetime hour to minute type.
    Code:
    create temp table tmp1( dt1 datetime year to second, dt2 datetime year to second);
    
    insert into tmp1 values ('2008-12-01 13:01:56', '2008-12-15 09:40:24') ;
    
    select dt1, dt2, dt1-dt2, dt2-dt1 from tmp1 ;
    
    select (dt2-dt1):: interval hour(9) to minute  from tmp1;
    will return:
    Code:
    dt1                 dt2                 (expression)       (expression)
    
    2008-12-01 13:01:56 2008-12-15 09:40:24       -13 20:38:28        13 20:38:28
    
    
    (expression)
    
           332:38
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Oct 2007
    Posts
    61
    Quote Originally Posted by ceinma
    maybe this can help you, but the datatype returned is a interval, to convert to datetime, need to convert to char an than to datetime hour to minute.
    But, be careful, because like this example, will return more than 24 hours what obvious is invalid to datetime hour to minute type.
    Code:
    create temp table tmp1( dt1 datetime year to second, dt2 datetime year to second);
    
    insert into tmp1 values ('2008-12-01 13:01:56', '2008-12-15 09:40:24') ;
    
    select dt1, dt2, dt1-dt2, dt2-dt1 from tmp1 ;
    
    select (dt2-dt1):: interval hour(9) to minute  from tmp1;
    will return:
    Code:
    dt1                 dt2                 (expression)       (expression)
    
    2008-12-01 13:01:56 2008-12-15 09:40:24       -13 20:38:28        13 20:38:28
    
    
    (expression)
    
           332:38


    Thanks This code will be very userfull to me...

    But Look the problem that I'm having with dates.
    Table x:
    A B Result
    2008-08-19 17:18 2008-08-19 14:00 0 03:1


    The field A and B are datefields... I'm trying do a simple select like

    select a,b,a-b from table x

    The right result should be 03:18 but it return 03:1....

    It's cause for me a lot of problems... It's a date format ?


    I found one way to convert 03:60 in hours for example:

    60 * 60 = 3600 / 100 = 36.

    So I got 3.36.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •