Solved: DATEDIFF and Msg 245 - 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

General SQL Server Forums

New to SQL Server Programming

Solved: DATEDIFF and Msg 245

Author

Topic

puterboy
Starting Member

4 Posts

Posted - 2009-02-17 : 23:11:28

Ok, so I'm doing some homework, and I've been asked to return a result from a table of all the employees that have been with the company longer than 15 years. Now, this seems beyond my comprehension, since we are only in week 5 of databases. Here is the code:

select Fname, Lname, hire_date, job_desc, datediff(yyyy, hire_date, getdate()) as 'Years of Service'
from Employee E, Jobs J
Where 'Years of Service' >= 15

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Years of Service' to data type int.

I can't for the life of my figure it out. I checked the MSDN on it, and all the data types match up. Like I said, we're in week 5. However, I enjoy a challenge (This wasn't supposed to be a challenge question), so I thought I'd do some research. I find the same problem on a lot of google results, but no answers...

Any ideas?

Camron

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 23:18:25

Any related keys between employee and job table?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 23:50:53

you cant use aliases directly in where clause. so make it like this


select *
from
(
select Fname, Lname, hire_date, job_desc, datediff(yyyy, hire_date, getdate()) as 'Years of Service'
from Employee E, Jobs J
)t
WHERE Years of Service >= 15

also as sodeep pointed out you need to have some relation b/w tables. the above query just returns you cartesian product b/w tables

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 23:55:49

select Fname, Lname, hire_date, job_desc, datediff(yyyy, hire_date, getdate()) as 'Years of Service'
from Employee E, Jobs J
Where datediff(yyyy, hire_date, getdate()) >= 15

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 04:49:20

quote:
Originally posted by visakh16

you cant use aliases directly in where clause. so make it like this

select *
from
(
select Fname, Lname, hire_date, job_desc, datediff(yyyy, hire_date, getdate()) as 'Years of Service'
from Employee E, Jobs J
)t
WHERE [Years of Service] >= 15

also as sodeep pointed out you need to have some relation b/w tables. the above query just returns you cartesian product b/w tables

Madhivanan

Failing to plan is Planning to fail

ke.neelima
Starting Member

6 Posts

Posted - 2009-02-18 : 06:12:51

Even the query writeen by you is correct.
The only change you need in it is the where condition..

select Fname, Lname, hire_date, job_desc, datediff(yyyy, hire_date, getdate()) as 'Years of Service'
from Employee E, Jobs J
Where datediff(yyyy, hire_date, getdate()) >= 15

Regards,
Neelima

Neelima

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 06:57:34

quote:
Originally posted by ke.neelima

Even the query writeen by you is correct.
The only change you need in it is the where condition..

select Fname, Lname, hire_date, job_desc, datediff(yyyy, hire_date, getdate()) as 'Years of Service'
from Employee E, Jobs J
Where datediff(yyyy, hire_date, getdate()) >= 15

Regards,
Neelima
Neelima

what is difference b\w mypost and this one

puterboy
Starting Member

4 Posts

Posted - 2009-02-18 : 09:55:37

@madhivanan Your solution returns an error message of no such column

@ke.neelima, @blkr Your solutions worked. Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 09:57:57

quote:
Originally posted by puterboy

@madhivanan Your solution returns an error message of no such column

@ke.neelima, @blkr Your solutions worked. Thank you

thats because you may have used different alias in your query post your used query.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 11:16:28

quote:
Originally posted by puterboy

@madhivanan Your solution returns an error message of no such column

@ke.neelima, @blkr Your solutions worked. Thank you

Do you need cartesion product of 2 tables?

puterboy
Starting Member

4 Posts

Posted - 2009-02-18 : 22:51:22

the query that worked ended up being:

select (Fname + ' ' + Lname) 'Name', hire_date, job_desc, datediff(year, hire_date, getdate()) 'Years of Service'
from Employees E, JobTypes J
where e.jobcode = j.jobcode and datediff(year, hite_date, getdate()) > 15

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 23:16:07

Modify like this so query uses index:

select (Fname + ' ' + Lname) 'Name', hire_date, job_desc, datediff(year, hire_date, getdate()) 'Years of Service'
from Employees E, JobTypes J
where e.jobcode = j.jobcode 
and Hire_date < DateAdd(yy,-15,getdate())

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-18 : 23:34:04

Sorry, but all the answers posted on this thread are wrong.

With this code you get an answer of 1 year of service, even though it is really 1 day:
select datediff(yyyy,'20081231','20090101')

You need to select where the hire_date is on or before 15 years before today, not where datediff shows a difference of 15 years.

Calculating the years of service is even more complex, so you should look in the link for the F_AGE_IN_YEARS function to see how to do that. It's going to be hard to slip that function call into your homework without the teacher knowing you didn't do the work, so good luck.

You also need to dump doing the join in the where clause and use the ANSI join syntax show below.

Finally, since this is for a class, you need to learn to test. Inspecting the results for a few well chosen dates would have shown you that your results were not correct.


select
	(Fname + ' ' + Lname) as [Name],
	hire_date,
	job_desc,
	-- Code for Function F_AGE_IN_YEARS is posted here
	-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462
	dbo.F_AGE_IN_YEARS( hire_date, getdate() ) as [Years of Service]
from
	Employees E
	inner join
	JobTypes J
	on e.jobcode = j.jobcode
where 
	hire_date <=
	-- Date 15 years before today at midnight
	dateadd(dd,datediff(dd,0,dateadd(yyyy,-15,getdate())),0)

CODO ERGO SUM

puterboy
Starting Member

4 Posts

Posted - 2009-02-21 : 17:43:14

@Micheal I see what you mean...Now, wouldn't it be just as effective to use datediff(month, hire_date, getDate()) / 12, or even seconds? I think that month would be close enough, and you wouldn't have to deal with leap years. Or seconds, as you can divide by the exact seconds in a year, also being able to ignore leap-year

I also learned that there are two types of joins, and am going to switch to ANSI style

In regards to my homework, I'm just converting to months, and I don't think it'll be a problem, since it's just wanting us to show our knowledge of the select statement and not the datediff function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources