# Thread: DataTime Calcule in SQL

1. Registered User
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. Registered User
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. Registered User
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;```

4. Registered User
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```

5. Registered User
Join Date
Oct 2007
Posts
61
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
•