SQL Query error

[amorous]

Hi all,

I have problem with the below query. I am using SQL server 2000 DB.
I tried the query in the query analyzer and it gave me the following error.

****
Invalid operator for data type. Operator equals multiply, type equals datetime.
******

The below is my query:

SELECT [OR Record].[Date of Surgery], 24*([Anesthesia stop time]-[Anesthesia start time]) AS Expr1
FROM [OR Record]
GROUP BY [OR Record].[Date of Surgery],24*([Anesthesia stop time]-[Anesthesia start time])

HAVING ((([OR Record].[Date of Surgery]) Between '10/01/2003' And '10/31/2003'));


The columns [Anesthesia stop time] and [Anesthesia start time] are in date-time format.
Thanks in advance.

VJ

 

[JayKusch]

You cannot use an arithmatic operand against a datetime variable ... 24*([Anesthesia stop time]

What are you trying to accomplish with the "24*"?


Thanks

J. Kusch

 

[amorous]

i am trying to calculate the total time in hours.

Thanks

VJ

 

[dreameR25]

You are attempting to find the difference between two dates but the result of :

Datetime - Datetime = Datetime.

Try using DateDiff function to get the difference between the two dates and this should solve your problem.

 

[dreameR25]

Actually, what you need is :

DATEDIFF (HH,[Anesthesia stop time], [Anesthesia start time])

This should give you the difference between the two dates in HOURS.

 

[amorous]

Thanks guys,

I tried using DATEDIFF function like
DATEDIFF(HH,[Anesthesia start time],[Anesthesia stop time])

if the start time is 8:25:00.000
and stop time is 9:00:00.00

The difference it gives me is '1' but how can i get the minutes, i mean in the above case the difference was 35 minutes. How can i convert this into a fraction representing hours.

And i also experienced the following error and i have no idea abt it.

***
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

***

Any suggestions.

Thanks

VJ

 

[kkielak]

Hello,

can't you use

DATEDIFF(MI,....)

to get time difference in minutes. Look in BOL

Chris

 

[sunila7]

Try this query: Iam not really sure why you are grouping by both Date of surgey and the differen\ce between start time and stop time .


SELECT [Date of Surgery], cast(seconds/3600 as varchar) + ' Hrs ' +
cast((seconds%3600)/60 as varchar) + ' mins ' +
cast((seconds%3600)%60 as varchar(2)) + ' Secs '
FROM
(SELECT [OR Record].[Date of Surgery],
DATEDIFF(ss,[Anesthesia start time],[Anesthesia stop time]) AS Seconds
FROM [OR Record]
GROUP BY [OR Record].[Date of Surgery],Seconds
HAVING ((([OR Record].[Date of Surgery]) Between '10/01/2003' And '10/31/2003'))) TBL

Sunil