| Author |
Topic |
|
jennypretty
Yak Posting Veteran
96 Posts |
 Posted - 2004-10-13 : 15:56:04
|
| Hello friends,I am trying to create a view like this:create view em_summary ASselect emp_name,(isnull(emp_salary,0)+isnull(emp_total,0)/(emp_end_date - emp_start_date+1)) AS em_averagefrom blah....emp_end_date & emp_start_date data types is DATETIME, when I do subtraction, it will become a number type. But It keeps generating errors:"Invalid operator for data type. Operator equals divide, type equals datetime"Can you please help me?Thanks.Jenny.The stupid question is the question you don't ask.www.single123.com |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-13 : 15:58:17
|
| Use the DateDiff functionBrett8-) |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-13 : 16:12:21
|
Here's what you are assuming:quote:
when I do subtraction, it will become a number type
Here's what you told us SQL Server is telling you:quote:
Invalid operator for data type. Operator equals divide, type equals datetime
Any thoughts as to why things might not be working as you expect?Read the error messages, and don't make assumptions wihtout testing them you. your best friend is Query Analyzer. Instead of assuming things work as you would like (or hope), try things out:declare @d1 datetime;declare @d2 datetime;set @d1 = '1/1/2004'set @d2 = '4/1/2004'select @d1-@d2And see what happens. Experiment, step away from your problem and try it out step by step, think logiclly about what you are trying to do and what the errors you are getting tell you. and if you are thinking "how can i get the difference between two dates", maybe do a search in books on-line to see what it tells you.- Jeff |
|
|
|
jennypretty
Yak Posting Veteran
96 Posts |
Posted - 2004-10-13 : 16:19:14
|
| My example is completely different. start_date is never bigger than end_date.That's why I have emp_end_date - emp_start_date.Jenny.The stupid question is the question you don't ask.www.single123.com |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-10-13 : 16:24:44
|
ok, jenny. sorry. Did it occur to you to make things "apples to apples" you could try select @d2-@d1 to see what happens?You are completely missing the entire point.  - Jeff |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-10-13 : 21:53:44
|
what he's trying to tell you is to experiment, explore why things are happening the way they are. data types were designed to be used accordingly. probably in other programming language int1/(date1-date2) might work but in sql, you can't do this since int1 and (date1 and date2) are different data types. you have convert/cast for this or datediff as brett mentioned.basically whatever error message you get from qa will tell you the problem.  --------------------keeping it simple... |
|
|
|
jennypretty
Yak Posting Veteran
96 Posts |
Posted - 2004-10-14 : 09:24:50
|
| Hello Jen & Smith, it worked. WOH WOH WOH.....I used CAST to convert the (d1-d2), then do the ADD or SUbtract, it worked. This is the format,select emp_name,(isnull(emp_salary,0)+isnull(emp_total,0)/CAST(emp_end_date AS INT - CAST(emp_start_date AS INT)+1) AS em_averagefrom blah....Thanks very much , guys.Jenny.The stupid question is the question you don't ask.www.single123.com |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-14 : 09:32:10
|
the cleaner version would be using datediffselect emp_name,(isnull(emp_salary,0)+isnull(emp_total,0)/(datediff(dd,emp_end_date,emp_start_date)+1) AS em_averagefrom blah....whatever worksCorey |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-14 : 09:36:33
|
| Wouldn't you be better off using DATEDIFF(Day, emp_start_date, emp_end_date)instead ofCAST(emp_end_date AS INT) - CAST(emp_start_date AS INT)??Kristen |
|
|
|
jennypretty
Yak Posting Veteran
96 Posts |
Posted - 2004-10-14 : 09:36:38
|
| Woh, it also worked.I wish you could have told me this before so I didn't wast about 7 hours trying to figure it out. Anyhow, I 've learned more.....I deleted mine and used yours.Thanks.Jenny.The stupid question is the question you don't ask.www.single123.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-14 : 09:37:09
|
 |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-10-14 : 09:39:15
|
quote:
Originally posted by Kristen
[snipped]
Corey |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-14 : 09:42:08
|
| "I wish you could have told me this before so I didn't wast about 7 hours trying to figure it out."Now then, as folk around here know, I'm a very patient and polite person, but you are pushing me, and I suspect everyone else around here, to the limit.Brett gave you that answer 2 minutes and 13 seconds after you posted the question.I'm glad the other SELECT DATEDIFF(Minute, '00:02:13', '07:00:00') wasn't on my payrollKristen |
|
|
|
jennypretty
Yak Posting Veteran
96 Posts |
Posted - 2004-10-14 : 09:55:46
|
| Oh yeah! thanks Brett.BIG THANKS.....JENNY.The stupid question is the question you don't ask.www.single123.com |
|
|
|
|
INVALID OPERATOR FOR DATA TYPE