sql query

[judithSampathwa]

hi
i have a question relating to date.

i have a table in a database which has the start date of an employee. what i want to do is i need to know how many years have he been working.

so i need to get the current date and substract it with the current date.

how can i do this?

[BitBlt]

Here's a code snippet to demonstrate the functions you will need:

declare @myDate datetime
set @myDate = '1/1/2001'

select @myDate as StartDate, getdate() as today, DATEDIFF(YEAR, @myDate, GETDATE()) as years

Just replace the datetime variable with your column name in a "select" from the table you need, and you should be fine.

Good luck, and good coding!

[judithSampathwa]

Here's a code snippet to demonstrate the functions you will need:

declare @myDate datetime
set @myDate = '1/1/2001'

select @myDate as StartDate, getdate() as today, DATEDIFF(YEAR, @myDate, GETDATE()) as years

Just replace the datetime variable with your column name in a "select" from the table you need, and you should be fine.

Good luck, and good coding!

hi
thank you very much

but there is a problem you had put jan 1, for the start date and today is Aug 12th 2011.
exactly it is 1 year and 7 months.

but say you change the start date to sep 12th 2001 and today's date as aug 12th 2011 then it is 9 years and 11 months. but still the code says it is 10 years how can i fix this bug???

[judithSampathwa]

i got it
below is the code

select EID, FirstName, StartDate, (DATEDIFF(MONTH, StartDate, GETDATE()) / 12) as Years
From Emp
Where DATEDIFF(MONTH, StartDate, GETDATE()) / 12 = 5

thank you