Problem with the TIME field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with the TIME field

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-12-10 : 12:57:24

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| -1353

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

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-10 : 13:03:47

Well there you have it...the definitive reason for storing the date portion...

Lets say you did this


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

How would you know it was ONLY 1 day?

Brett

8-)

dupati1
Posting Yak Master

123 Posts

Posted - 2003-12-10 : 13:13:43

Hi Brett,

Thanks a lot for the reply. I never realized that this a much bigger problem than what i thought.

Ya i cant be sure that this type of discrepancy is for only 1 day.

But i am not sure how to solve this problem now as i did not store the date portion.

Any Suggestions to overcome this problem??

I tried your query but i got the value 0 for Diftime.

I dont know whats wrong, Any ideas??

Thanks a lot.

VJ

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-10 : 13:28:00

That's cause I AM A TOTAL SCRUB



USE Northwind
GO

CREATE TABLE xORRecord(TimeOperationBegan datetime, TimeOperationEnded datetime)
GO

INSERT INTO xORRecord (TimeOperationBegan, TimeOperationEnded)
SELECT '1899-12-30 10:40:00.000','1899-12-30 11:22:00.000'UNION ALL
SELECT '1899-12-30 11:41:00.000', '1899-12-30 11:52:00.000'UNION ALL
SELECT '1899-12-30 16:03:00.000', '1899-12-30 16:32:00.000'UNION ALL
SELECT '1899-12-30 23:05:00.000', '1899-12-30 00:32:00.000'
GO

SELECT    TimeOperationBegan
	, TimeOperationEnded
	, CASE  WHEN TimeOperationBegan < TimeOperationEnded 
		THEN DATEDIFF(mi,TimeOperationBegan,TimeOperationEnded)
		ELSE DATEDIFF(mi,TimeOperationBegan,DATEADD(d,1,TimeOperationEnded))
	  END
	  AS Diftime
  FROM    xORRecord
GO

DROP TABLE xORRecord
GO

As far as the other problem...Looking at your subject, if the patient is being operated on for over 24 hours, I think s/he'd be dead...

That should get you through the overnight period....

Do you work with the ICD-9 sheets?

Or is this a separate OR tracking app?

Brett

8-)

dupati1
Posting Yak Master

123 Posts

Posted - 2003-12-10 : 13:57:09

Thanks Brett,

Your query works like a charm. its just a different application to track patient records.

Thanks a million.

VJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources