Problem with TIME field

[amorous]

Hi,

I am using SQL Server 2000.

I made this following simple query:

SELECT [TimeOperationBegan],[TimeOperationEnded],
(DATEDIFF(minute,[TimeOperationBegan],[TimeOperationEnded])) AS Diftime
FROM [ORRecord]

The sample results are as follows:

TimeOperationBegan |TimeOperationEnded |Diftime
__________________________________________________________
1899-12-30 10:40:00.000| 1899-12-30 11:22:00.000| 42
1899-12-30 11:41:00.000| 1899-12-30 11:52:00.000| 11
1899-12-30 16:03:00.000| 1899-12-30 16:32:00.000| 29
1899-12-30 23:05:00.000| 1899-12-30 00:32:00.000| -1352

How can i get rid of this negative value and make the query to calculate the difference between 23:05 and 24:32 for the fourth record??

Thanks in advance.

VJ

 

[melontek]

I think you have an issue with the data. It appears that the time is reversed. The operation ended at 12:32 AM and began at 11:05 PM. Since the end time is less than the begin time, you can't avoid the negative (unless you swap the values).

 

[jby1]

It looks like you are disregarding the date portion of the datetime field. however, the query will not do this. If the date portion is genuinely not significant, then you could try doing something like :

SELECT [TimeOperationBegan],[TimeOperationEnded],
(DATEDIFF(minute,[TimeOperationBegan],[TimeOperationEnded])) AS Diftime
FROM [ORRecord] 
WHERE TimeOperationEnded >=  TimeOperationBegan
UNION
SELECT [TimeOperationBegan],[TimeOperationEnded],
(DATEDIFF(minute,DATEADD(d,1,[TimeOperationBegan]),[TimeOperationEnded])) AS Diftime
FROM [ORRecord] 
WHERE TimeOperationEnded <  TimeOperationBegan

Although you might want to review the way you are using the datatype, as this isn't really a good way of doing things.

(PS I am not saying that the UNION is the best way of doing this either, just demonstrating one way of doing it!)

 

[melontek]

My bad...misread your post. Sorry about that.

I would check to see how you are entering that date. I would use the date as well as the time. DATEDIFF won't ignore the date.

 

[amorous]

Thanks Guys,

I tried this query, but its not working. I get the value 0 in the diftime column. Any ideas??

SELECT TimeOperationBegan
, TimeOperationEnded
, CASE WHEN TimeOperationBegan < TimeOperationEnded
THEN DATEDIFF(mm,TimeOperationBegan,TimeOperationEnded)
ELSE DATEDIFF(mm,TimeOperationBegan,DATEADD(d,1,TimeOperationEnded))
END
AS Diftime
FROM ORRecord


Thanks
VJ

 

[amorous]

Hi Guys,

Please ignore my previous post. I figured it out myself.

Thank you so much for your invaluable suggestions.

VJ