Hi All

In my C# web app project, there is a situation in which if a person's age is greater than or equal to 60,he should retire.
my sql query is:

select datediff(year,(select DateofBirth from employee_table where empno=5),getdate()) as 'Worked Years'

If worked Years is >= 60, his name will come. Now how can i recontruct this query such that i get his name 3 months before he actually retires?

I don't think you want to put the field name "Worked Years" in single quotes. That is usually reserved for static text values. You might try replacing it with [Worked Years] or use double quotes. As a side note, from personal experience, you should avoid using spaces and special characters in the names of things like fields or tables or databases. Try to stick to the Underscore character "_" so you would get [Worked_Years].

What you don't know can hurt you!

Thanks for the advice David, i will keep it in mind.
I have written a function:

ALTER FUNCTION fn_retire(@empno varchar(10))
RETURNS varchar(3000)
AS

BEGIN
    -- Declare the return variable here
    Declare @TempWork varchar(1000)
    DECLARE @Work varchar(1000)
    SELECT @TempWork = datediff(year,(select DateofBirth from employee_table where empno=@empno),getdate())

    -- Add the T-SQL statements to compute the return value here
    IF(@TempWork <> '')
    Begin
       If(@TempWork >= 60)
          Begin
              Set @TempWork = @Work
          End
    End

    -- Return the result of the function
    RETURN @Work

END
GO

This function will return all those empno's whose age is greater than or equal to 60. What i want is that i want all those empno's 3 months before in advance. All records pertaining to this should reflect 3 months before, what changes are required for this in function? Please shed some light!

--Abhishek

Abhishek,

To get a recordset of employees who were > 60 years old 3 months ago you might try comparing the employees birthdate against getdate() - 90. I'm not sure how SQLServer treats date. Odds are that expression won't work. Maybe dateadd('d', -90, getdate()) might work.

Also I don't know if you pasted that function in or mistyped it. But it looks like @Work is never assigned a value so it would always return a null value.


What you don't know can hurt you!

Yes, getDate()-90 is indeed valid.

Minor caution: getDate() returns the current date *AND TIME*, to this will actually be the date AND time exactly 90 days before the current time.

But for almost all purposes, this is good enough.

The standard way to convert getDate() to just a date is something like this:

    CONVERT( DATETIME, CONVERT( VARCHAR(20), getDate(), 112 ), 112 )

So if you are fanatic, use

    CONVERT( DATETIME, CONVERT( VARCHAR(20), getDate()-90, 112), 112 )

or even

    CONVERT( DATETIME, CONVERT( VARCHAR(20), DATEADD(month,-3,getDate()), 112), 112)

Ugly, isn't it? Personally, I'd just use
    getDate()-90

I think my requirement was misunderstood. Let me explain by an example:
Suppose John is going to retire on 21st September 2008,age>60. If today's date is 18th June, then actually i should get the record on 21st June, 3 months before he actually retires. I think i am clear now.

-- Abhishek

So my last answer does that:

 CONVERT( DATETIME, CONVERT( VARCHAR(20), DATEADD(month,-3,getDate()), 112), 112)

Well, it's probably easier to do this backwards from that:

"On each date, you look to see if each person's 60th birthday will occur exactly 3 months in the future."

Is that what you need???

The problem with DATEDIFF is that it *ignores* units smaller than the unit you use for the diff.

For example
    DATEDIFF( year, '12/31/2007', '1/1/2008' )
is *ONE*!!! Even though the actual difference is only 1 day! All that DATEDIFF does is subtract the two years.

So how about this:

Code:

CREATE PROCEDURE findThoseRetiringInThreeMonths
AS
DECLARE @threeMonths

SET @threeMonths = CONVERT(DATETIME, CONVERT(VARCHAR(20), DATEADD(month,3,getDate()), 112), 112)

SELECT * FROM employee_table 
WHERE YEAR(DateOfBirth) = YEAR(@threeMonths)-60
  AND MONTH(DateOfBirth) = MONTH(@threeMonths)
  AND DAY(DateOfBirth) = DAY(@threeMonths)

I think that does it. Finds all people whose 60th birthday is *exactly* 3 months from today.

Now, what I want to know: What do you do about people like me who are more than 3 years past that date, already. And who aren't going to retire for another 3 or 4 or 5 or 6 years?

Maybe I should retire. *sigh*

Can do that more simply, of course:

Code:

CREATE PROCEDURE findThoseRetiringInThreeMonths
AS
DECLARE @threeMonths

SET @threeMonths = CONVERT(DATETIME, CONVERT(VARCHAR(20), DATEADD(month,3,getDate()), 112), 112)

SELECT * FROM employee_table 
WHERE DATEADD(year,60,DateOfBirth) = @threeMonths

And now, if you wish, you can change that last = to >= and catch all the people like me.

Thanks Pedant for showing this nice procedure.Admired :), i was not thinking that reverse way.
According to the functionality which has been given to me, the employee will retire automatically upon attaining 60 years of age, no employee is allowed to work after that.

-- Abhishek