datepart function not working properly 1

[jposner]

In my query below for terminated employees, I need the datepart function to return terminated employees up to the date entered but only from the year of the date entered.

So if a user enters the date 2/1/04 they should see all employees that have terminated in 2004, through Feb. 1st.

However, what is happening is that I am only seeing employees terminated AFTER or EQUAL to the date specified.

Here is that part of the query:

Eemploy.Eestatus = 'Terminated' AND Eemploy.Eedateend IS NULL AND Ejob.EjDateEnd = Eemploy.Eetermdate and eemploy.eetermdate IN(DATEPART(YEAR, GetDate()))

Any ideas?

 

[zhavic]

What about this:

Eemploy.Eestatus = 'Terminated' AND Eemploy.Eedateend IS NULL AND Ejob.EjDateEnd = Eemploy.Eetermdate and YEAR( eemploy.eetermdate ) = YEAR( @tDate_entered ) AND eemploy.eetermdate <= @tDate_entered

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[jposner]

THANKS!! worked perfect

 

[rdroske]

DATEPART(YEAR, GetDate()) is always going to return the year of the current date (right now always 2004) so for starters you probably want to use the date they enter instead of GetDate().

As to the rest it depends on what you have in the fields.

Assuming eemploy.eetermdate is a full date you probably want just the year part of that and an = compare.