time conversion hiccup

**Pall Bjornsson** · Sep 18 '07, 02:45 PM

Re: time conversion hiccup

Hi !

What I can see via quick read are two errors or mistakes.

1) Definition of a variable or result of type decimal(1), can store at the
most one total number of digits both to the left and to the right of the
decimal point, so you'll never get a result with anything more than a single
digit number, even if the result should be 10 or more, in which case you
should get an overflow error.

2) The division by the integer number 60 forces the operation to be an
integer division, as you can easily see by executing this statement:
select datediff(n,'08: 00','14:25')/60,

convert(decimal (1),datediff(n, '08:00','14:25' )/60),

datediff(n,'08: 00','14:25')/60.0,

convert(decimal (1),datediff(n, '08:00','14:25' )/60.0)

Hope this helps,

Palli

<DonLi2006@gmai l.comwrote in message
news:1190084992 .933315.305940@ g4g2000hsf.goog legroups.com...

Hi,
>
ddl & dml
project varchar(10) start char(5) stop char(5)
------------------------- ----- -----
hey now 21:00 19:25
new test 20:25 20:30
t 10 21:00 NULL
t 11 21:10 21:35
t 12 21:30 22:40
t 12 7:05 11:10
test me 08:00 14:25
test me 17:00 17:55
>
what I want is to calculate time duration using hour (h.1decimal) e.g.
1.2 :
what I have now using the following query:
select project, start, stop,
CASE WHEN (datediff(n,sta rt,stop) < 0) THEN -1
WHEN (datediff(n,sta rt,stop) < 1) THEN (CAST(datediff( n,start,stop)
as decimal(1)))
ELSE Convert(decimal (1),(datediff(n ,start,stop)/60)) END as
total_hours
from testTBl
group by project, start, stop
>
output:
project start stop total_hours
------------------------- ----- ----- -----------
hey now 21:00 19:25 -1
new test 20:25 20:30 0
t 10 21:00 NULL NULL
t 11 21:10 21:35 0
t 12 21:30 22:40 1
t 12 7:05 11:10 4
test me 08:00 14:25 6
test me 17:00 17:55 0
>
If the calcuate is right I'd like to remove start and stop columns,
so, it would just return project and the sum of hours including less
than an hour in decimal for each.
>
Thank you.
>

**DonLi2006@gmail.com** · Sep 18 '07, 04:05 PM

Re: time conversion hiccup

Beautiful, thank you.

On Sep 18, 9:43 am, "Pall Bjornsson" <pa...@kvos.isw rote:

Hi !
>
What I can see via quick read are two errors or mistakes.
>
1) Definition of a variable or result of type decimal(1), can store at the
most one total number of digits both to the left and to the right of the
decimal point, so you'll never get a result with anything more than a single
digit number, even if the result should be 10 or more, in which case you
should get an overflow error.
>
2) The division by the integer number 60 forces the operation to be an
integer division, as you can easily see by executing this statement:
select datediff(n,'08: 00','14:25')/60,
>
convert(decimal (1),datediff(n, '08:00','14:25' )/60),
>
datediff(n,'08: 00','14:25')/60.0,
>
convert(decimal (1),datediff(n, '08:00','14:25' )/60.0)
>
Hope this helps,
>
Palli
>
<DonLi2...@gmai l.comwrote in message
>
news:1190084992 .933315.305940@ g4g2000hsf.goog legroups.com...
>
>
>

Hi,

OP omitted
- Show quoted text -

**DonLi2006@gmail.com** · Sep 19 '07, 12:15 AM

Re: time conversion hiccup

ahe, I spoke a bit too soon, new prob.
data sets:
start stop
19:30 02:15 (next day morning)
26:15 (invalid hh:mm time range)

CASE WHEN (datediff(n,sta rt,stop) < 0) THEN 0 END

above stmt not good, what now? got to go eat, could you help me to
think, oh, you may ask, may I eat for you as well? :) thanks a
billion...

On Sep 18, 10:58 am, DonLi2...@gmail .com wrote:

Beautiful, thank you.
>
On Sep 18, 9:43 am, "Pall Bjornsson" <pa...@kvos.isw rote:
>
>
>

Hi !

>

What I can see via quick read are two errors or mistakes.

>

1) Definition of a variable or result of type decimal(1), can store at the
most one total number of digits both to the left and to the right of the
decimal point, so you'll never get a result with anything more than a single
digit number, even if the result should be 10 or more, in which case you
should get an overflow error.

>

2) The division by the integer number 60 forces the operation to be an
integer division, as you can easily see by executing this statement:
select datediff(n,'08: 00','14:25')/60,

>

convert(decimal (1),datediff(n, '08:00','14:25' )/60),

>

datediff(n,'08: 00','14:25')/60.0,

>

convert(decimal (1),datediff(n, '08:00','14:25' )/60.0)

>

Hope this helps,

>

Palli

>

<DonLi2...@gmai l.comwrote in message

>

news:1190084992 .933315.305940@ g4g2000hsf.goog legroups.com...

>

Hi,

OP omitted
- Show quoted text -- Hide quoted text -

>
- Show quoted text -

**Ed Murphy** · Sep 19 '07, 02:35 PM

Re: time conversion hiccup

DonLi2006@gmail .com wrote:

ahe, I spoke a bit too soon, new prob.
data sets:
start stop
19:30 02:15 (next day morning)
26:15 (invalid hh:mm time range)
>
CASE WHEN (datediff(n,sta rt,stop) < 0) THEN 0 END

Assuming that the stop time is always within 24 hours after the
start time:

case
when datediff(n,star t,stop) < 0
then datediff(n,star t,stop) + 1440 -- minutes per day
else datediff(n,star t,stop)
end

**DonLi2006@gmail.com** · Sep 19 '07, 09:55 PM

Re: time conversion hiccup

Yeah, I solved it in a similar fasion this morning, sorry for the late
update.

On Sep 19, 9:32 am, Ed Murphy <emurph...@soca l.rr.comwrote:

DonLi2...@gmail .com wrote:

ahe, I spoke a bit too soon, new prob.
data sets:
start stop
19:30 02:15 (next day morning)
26:15 (invalid hh:mm time range)

>

CASE WHEN (datediff(n,sta rt,stop) < 0) THEN 0 END

>
Assuming that the stop time is always within 24 hours after the
start time:
>
case
when datediff(n,star t,stop) < 0
then datediff(n,star t,stop) + 1440 -- minutes per day
else datediff(n,star t,stop)
end

**tatata9999@gmail.com** · Oct 3 '07, 03:45 PM

Re: time conversion hiccup

On Sep 18, 9:43 am, "Pall Bjornsson" <pa...@kvos.isw rote:

Hi !
>
What I can see via quick read are two errors or mistakes.
>
1) Definition of a variable or result of type decimal(1), can store at the
most one total number of digits both to the left and to the right of the
decimal point, so you'll never get a result with anything more than a single
digit number, even if the result should be 10 or more, in which case you
should get an overflow error.
>
2) The division by the integer number 60 forces the operation to be an
integer division, as you can easily see by executing this statement:
select datediff(n,'08: 00','14:25')/60,
>
convert(decimal (1),datediff(n, '08:00','14:25' )/60),
>
datediff(n,'08: 00','14:25')/60.0,
>
convert(decimal (1),datediff(n, '08:00','14:25' )/60.0)
>
Hope this helps,
>
Palli
>
<DonLi2...@gmai l.comwrote in message
>
news:1190084992 .933315.305940@ g4g2000hsf.goog legroups.com...
>
>
>

Hi,

>

ddl & dml
project varchar(10) start char(5) stop char(5)
------------------------- ----- -----
hey now 21:00 19:25
new test 20:25 20:30
t 10 21:00 NULL
t 11 21:10 21:35
t 12 21:30 22:40
t 12 7:05 11:10
test me 08:00 14:25
test me 17:00 17:55

>

what I want is to calculate time duration using hour (h.1decimal) e.g.
1.2 :
what I have now using the following query:
select project, start, stop,
CASE WHEN (datediff(n,sta rt,stop) < 0) THEN -1
WHEN (datediff(n,sta rt,stop) < 1) THEN (CAST(datediff( n,start,stop)
as decimal(1)))
ELSE Convert(decimal (1),(datediff(n ,start,stop)/60)) END as
total_hours
from testTBl
group by project, start, stop

>

output:
project start stop total_hours
------------------------- ----- ----- -----------
hey now 21:00 19:25 -1
new test 20:25 20:30 0
t 10 21:00 NULL NULL
t 11 21:10 21:35 0
t 12 21:30 22:40 1
t 12 7:05 11:10 4
test me 08:00 14:25 6
test me 17:00 17:55 0

>

If the calcuate is right I'd like to remove start and stop columns,
so, it would just return project and the sum of hours including less
than an hour in decimal for each.

>

Thank you.- Hide quoted text -

>
- Show quoted text -

Hi, there's a bug. The following query would return what is expected,
good.
select pkCol, cddate as origdate,conver t(char,cddate,1 01) as
ddate, start, stop, project,
CASE WHEN (datediff(n,sta rt,stop) < 0) THEN
Left((datediff( n,start,'23:59' ) + datepart(n,'200 7-09-19
10:01:00') + datediff(n,'00: 00',stop))/60.0,4)
ELSE Left(datediff(n ,start,stop)/60.0,4) END as
hours_spent
from testTBL

However, when switching to sum function for the above like, I'm
getting invalid results, the culprit seems to be the entry/entries
with two dates overlap, see a sample below the following query? And
the odd thing is, when I tested the query against this particular
entry, it generated correct resultset (summary), but not a query like
the one below, how come and more importantly how to fix it? Thanks.
select project,
CASE WHEN (SUM(datediff(n ,start,stop)/60.0) < 0)
THEN Left(SUM((dated iff(n,start,'23 :59') +
datepart(n,'200 7-09-19 10:01:00') + datediff(n,'00: 00',stop))/60.0),
4)
WHEN (SUM(datediff(n ,start,stop)/60.0) 0)
THEN Left(SUM(datedi ff(n,start,stop )/60.0),4) End as
total_hours
from testTBL
group by project

cddate project start stop
----------- ------------ ----- ----- ----------- ------
10/2/2007 hey now 23:05 1:15

**Erland Sommarskog** · Oct 3 '07, 10:15 PM

Re: time conversion hiccup

(tatata9999@gma il.com) writes:

Hi, there's a bug. The following query would return what is expected,
good.
select pkCol, cddate as origdate,conver t(char,cddate,1 01) as
ddate, start, stop, project,
CASE WHEN (datediff(n,sta rt,stop) < 0) THEN
Left((datediff( n,start,'23:59' ) + datepart(n,'200 7-09-19
10:01:00') + datediff(n,'00: 00',stop))/60.0,4)
ELSE Left(datediff(n ,start,stop)/60.0,4) END as
hours_spent
from testTBL
>
However, when switching to sum function for the above like, I'm
getting invalid results, the culprit seems to be the entry/entries
with two dates overlap, see a sample below the following query? And
the odd thing is, when I tested the query against this particular
entry, it generated correct resultset (summary), but not a query like
the one below, how come and more importantly how to fix it? Thanks.
select project,
CASE WHEN (SUM(datediff(n ,start,stop)/60.0) < 0)
THEN Left(SUM((dated iff(n,start,'23 :59') +
datepart(n,'200 7-09-19 10:01:00') + datediff(n,'00: 00',stop))/60.0),
4)
WHEN (SUM(datediff(n ,start,stop)/60.0) 0)
THEN Left(SUM(datedi ff(n,start,stop )/60.0),4) End as
total_hours
from testTBL
group by project

It could help if you posted the CREATE TABLE statement for the table,
INSERT statments with sample data, and the desired result. I can't
exactly see what you are looking for. But one think looks funny to
me: you have SUM on every expression in the CASE. I would expect the
SUM to be around the entire CASE. But as I said, I don't know what
this query is supposed to achieve.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**tatata9999@gmail.com** · Oct 4 '07, 12:15 AM

Re: time conversion hiccup

On Oct 3, 5:00 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

(tatata9...@gma il.com) writes:

Hi, there's a bug. The following query would return what is expected,
good.
select pkCol, cddate as origdate,conver t(char,cddate,1 01) as
ddate, start, stop, project,
CASE WHEN (datediff(n,sta rt,stop) < 0) THEN
Left((datediff( n,start,'23:59' ) + datepart(n,'200 7-09-19
10:01:00') + datediff(n,'00: 00',stop))/60.0,4)
ELSE Left(datediff(n ,start,stop)/60.0,4) END as
hours_spent
from testTBL

>

However, when switching to sum function for the above like, I'm
getting invalid results, the culprit seems to be the entry/entries
with two dates overlap, see a sample below the following query? And
the odd thing is, when I tested the query against this particular
entry, it generated correct resultset (summary), but not a query like
the one below, how come and more importantly how to fix it? Thanks.
select project,
CASE WHEN (SUM(datediff(n ,start,stop)/60.0) < 0)
THEN Left(SUM((dated iff(n,start,'23 :59') +
datepart(n,'200 7-09-19 10:01:00') + datediff(n,'00: 00',stop))/60.0),
4)
WHEN (SUM(datediff(n ,start,stop)/60.0) 0)
THEN Left(SUM(datedi ff(n,start,stop )/60.0),4) End as
total_hours
from testTBL
group by project

>
It could help if you posted the CREATE TABLE statement for the table,
INSERT statments with sample data, and the desired result. I can't
exactly see what you are looking for. But one think looks funny to
me: you have SUM on every expression in the CASE. I would expect the
SUM to be around the entire CASE. But as I said, I don't know what
this query is supposed to achieve.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx- Hide quoted text -
>
- Show quoted text -

Erland,

You're the Man! Thank you.

**tatata9999@gmail.com** · Oct 4 '07, 12:26 AM

Re: time conversion hiccup

On Oct 3, 5:00 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

(tatata9...@gma il.com) writes:

Hi, there's a bug. The following query would return what is expected,
good.
select pkCol, cddate as origdate,conver t(char,cddate,1 01) as
ddate, start, stop, project,
CASE WHEN (datediff(n,sta rt,stop) < 0) THEN
Left((datediff( n,start,'23:59' ) + datepart(n,'200 7-09-19
10:01:00') + datediff(n,'00: 00',stop))/60.0,4)
ELSE Left(datediff(n ,start,stop)/60.0,4) END as
hours_spent
from testTBL

>

However, when switching to sum function for the above like, I'm
getting invalid results, the culprit seems to be the entry/entries
with two dates overlap, see a sample below the following query? And
the odd thing is, when I tested the query against this particular
entry, it generated correct resultset (summary), but not a query like
the one below, how come and more importantly how to fix it? Thanks.
select project,
CASE WHEN (SUM(datediff(n ,start,stop)/60.0) < 0)
THEN Left(SUM((dated iff(n,start,'23 :59') +
datepart(n,'200 7-09-19 10:01:00') + datediff(n,'00: 00',stop))/60.0),
4)
WHEN (SUM(datediff(n ,start,stop)/60.0) 0)
THEN Left(SUM(datedi ff(n,start,stop )/60.0),4) End as
total_hours
from testTBL
group by project

>
It could help if you posted the CREATE TABLE statement for the table,
INSERT statments with sample data, and the desired result. I can't
exactly see what you are looking for. But one think looks funny to
me: you have SUM on every expression in the CASE. I would expect the
SUM to be around the entire CASE. But as I said, I don't know what
this query is supposed to achieve.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx- Hide quoted text -
>
- Show quoted text -

oops, I hit the response button too fast. Now,
option a:
SUM(CASE WHEN (datediff(n,sta rt,stop)/60 0)
THEN (datediff(n,sta rt,stop)/60) End) as
total_hours
returned summary/calculated about right, but it's at hour level, so,
0.45 minutes would be discarded, not very good

option b:
SUM(CASE WHEN (datediff(n,sta rt,stop)/60 0)
THEN (datediff(n,sta rt,stop)/60.0) End) as
total_hours
returned bloated up data (too much), not good at all

What else? As always, many thanks.

**Ed Murphy** · Oct 4 '07, 02:35 AM

Re: time conversion hiccup

tatata9999@gmai l.com wrote:

SUM(CASE WHEN (datediff(n,sta rt,stop)/60 0)
THEN (datediff(n,sta rt,stop)/60) End) as
total_hours
returned summary/calculated about right, but it's at hour level, so,
0.45 minutes would be discarded, not very good

CASTing datediff() to some appropriate DECIMAL type should take
care of it.

**tatata9999@gmail.com** · Oct 4 '07, 10:35 PM

Re: time conversion hiccup

On Oct 3, 9:30 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

tatata9...@gmai l.com wrote:

SUM(CASE WHEN (datediff(n,sta rt,stop)/60 0)
THEN (datediff(n,sta rt,stop)/60) End) as
total_hours
returned summary/calculated about right, but it's at hour level, so,
0.45 minutes would be discarded, not very good

>
CASTing datediff() to some appropriate DECIMAL type should take
care of it.

I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
sample one? Thanks.

**Ed Murphy** · Oct 5 '07, 01:05 AM

Re: time conversion hiccup

tatata9999@gmai l.com wrote:

On Oct 3, 9:30 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

>tatata9...@gma il.com wrote:

>> SUM(CASE WHEN (datediff(n,sta rt,stop)/60 0)
>> THEN (datediff(n,sta rt,stop)/60) End) as
>>total_hours
>> returned summary/calculated about right, but it's at hour level, so,
>>0.45 minutes would be discarded, not very good

>CASTing datediff() to some appropriate DECIMAL type should take
>care of it.

>
I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
sample one? Thanks.

Try DECIMAL(10,2) and see how that works for you.

**tatata9999@gmail.com** · Oct 5 '07, 03:35 AM

Re: time conversion hiccup

On Oct 4, 7:59 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

tatata9...@gmai l.com wrote:

On Oct 3, 9:30 pm, Ed Murphy <emurph...@soca l.rr.comwrote:

tatata9...@gmai l.com wrote:
> SUM(CASE WHEN (datediff(n,sta rt,stop)/60 0)
> THEN (datediff(n,sta rt,stop)/60) End) as
>total_hours
> returned summary/calculated about right, but it's at hour level, so,
>0.45 minutes would be discarded, not very good
CASTing datediff() to some appropriate DECIMAL type should take
care of it.

>

I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
sample one? Thanks.

>
Try DECIMAL(10,2) and see how that works for you.

Thank you, this is a good idea to try. Here's some sample result,
before I do that, let me refresh ddl a bit for clarity,
both start and stop columns are of char(5) nullable.
The query looks like this
select SUM(Convert(DEC IMAL(10,2), CASE WHEN (datediff(n,sta rt,stop)/60
< 0)
THEN (datediff(n,sta rt,'23:59') +
datepart(n,'200 7-09-19 10:01:00') + datediff(n,'00: 00',stop)/60.0)
WHEN (datediff(n,sta rt,stop)/60 0)
THEN (datediff(n,sta rt,stop)/60.0) ...
Also, I tried the DECIMAL(10,2) and its variants for a regular query,
then use app language to total it.
The difference is, the sum one is 94.80 hours while the regular query
is 90.24. Not satisfactory.

I've also looked up BOL for it, and tried different p/s variants to no
avail. Hmm, am I stuck?

**Erland Sommarskog** · Oct 5 '07, 07:25 AM

Re: time conversion hiccup

(tatata9999@gma il.com) writes:

I've tried DECIMAL(1) and (2) respectively to no avail. Do you have a
sample one? Thanks.

Decimal(1) means a number in the range 0-9 with no decimals.

I usually sort this out by simply multiplying with 1.0. Like in many other
languages, / in T-SQL is integer division when two integers meet.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

time conversion hiccup

time conversion hiccup

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment