SQL Query Problem - 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)

SQL Query Problem

Author

Topic

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-06 : 10:02:29

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

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-06 : 10:07:30

A dateime subtracted froma datetime returns a datetime. Use the DATEDIFF function inside the brackets, then you can multiply by 24.

Raymond

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-06 : 10:08:34

You can't multiple a date...

SELECT GetDate() * 24

What are you trying to do?

Maybe you need to look at DATEADD

Brett

8-)

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 10:08:43

you may use 24*convert(int, *([Anesthesia stop time]-[Anesthesia start time]) )

Try this and let me know. Thanks

Siva Cherukupally
Starting Member

16 Posts

Posted - 2003-11-06 : 10:10:50

you may use 24*convert(int, *([Anesthesia stop time]-[Anesthesia start time]) )

Try this and let me know. Thanks

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-06 : 10:52:32

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

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-06 : 10:59:08

Hi VJ

DATEDIFF can be used with 'mm' instead of 'hh' - check out BOL.

Re your last error, I can only assume it relates to your HAVING clause. It may depend on your server date setting how it interprets '10/31/2003'. I always find it safest to use the ANSI format of 'yyyymmdd'.

Raymond

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 11:00:55

why are you grouping at all?

- Jeff

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-06 : 11:18:11

Actually i wanted to display the results in the following format.

Date of surgery Sum of Hours
10/01/2003 1.5
10/02/2003 5.0
10/03/2003 9.4

I mean the sum of hours column is like a running sum a cummulative one.

How can i do it?

I tried MM instead of HH but it gave me 0 as the difference between start time and stop time.

Any suggestions.

VJ

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-06 : 11:41:21

Apologies, my fault for mis-remembering! It's 'mi' and not 'mm'.

Raymond

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 12:23:44

how are you display the results of this SQL to the user? I.e., in a report, in ASP, in Excel, etc?

- Jeff

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-06 : 13:07:02

I am using Access 2000 front end and trying to display the results on the access report.

Now when i click on the button(named anesthesia hours)on one of the forms, i get a pop up window which asks for a parameter value for "mi". I dont know the reason.

I have in my query as :
(DATEDIFF(mi,[Anesthesia start time],[Anesthesia stop time])

But when i click "yes" or "cancel" on the pop window i get the message saying "you cancelled the previous action".

Any suggestions.

Thanks

VJ

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-06 : 13:42:45

do the running totals on the access report. just drop the field onto your report, right-click and choose properties, and select:

RunningSum = (Over all) or (Over Group)

and then you are done ! easy as pie. Don't try to force SQL server to make calculations it doesn't need to. Keep things simple.

- Jeff

dupati1
Posting Yak Master

123 Posts

Posted - 2003-11-07 : 09:27:30

Thanks a lot guys.

Your suggestions and ideas helped me.

VJ

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources