DataTime Calcule in SQL

dicipulofer · #1 (**permalink**) 08-22-08, 10:38

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 ?

dicipulofer · #2 (**permalink**) 08-22-08, 16:01

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 ?

ceinma · #3 (**permalink**) 08-23-08, 20:07

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;

ceinma · #4 (**permalink**) 08-23-08, 20:25

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

dicipulofer · #5 (**permalink**) 08-26-08, 13:50

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech