OK - glad I could help.

Paarthasarathy (2/10/2010)

---

Hi Wschampler

but the query does not work for this sample data..

insert into emp values(1,'CCCCC',10000,'abc','01/01/2005','n')

insert into emp values(1,'CCCCC',10000,'xys','01/11/2005','n')

insert into emp values(1,'CCCCC',20000,'xyz','01/01/2006','n')

insert into emp values(1,'CCCCC',20000,'xyy','01/01/2008','y')

i need this employee to get returned...

@Paarthasarathy: can it be there is an error in your sample data? The employees with ename AAAAA and BBBBB both have eid 1.

To test my solution, I changed the...

Maybe I'm missing something but could it be as simple as this?

SELECT *

FROM emp

WHERE act = 'y'

...

Try this

DECLARE @TodaysDate Date

DECLARE @TomorrowsDate Date

SET @TodaysDate = '20100214'

SET @TomorrowsDate = DATEADD(dd,1,@TodaysDate)

Adi Cohn-120898 (2/10/2010)

---

Unfortunately I have to admit that I was wrong....

PH,

You're welcome and thank you for your feedback.

I do recommend you reconsidering the design of your datamodel... If you explain why you think you need this construction, we may help...

Grant Fritchey (2/10/2010)

---

wschampheleer (2/10/2010)

---

pink_panther,

Thank you for the compliment.

Unfortunately, I believe your new version is no improvement: you still need to insert the old ID if you want to retrieve it...

pink_panther,

Thank you for the compliment.

Unfortunately, I believe your new version is no improvement: you still need to insert the old ID if you want to retrieve it using the OUTPUT...

What version of SQL Server are you using?

In SQL 2008 you have a date data type with a range of 0001-01-01 through 9999-12-31 (January 1, 1 A.D. through December 31,...

if you go for the above solution, you'll need to do some extra work afterwards. Here's why.

Don't have access to SQL2K anymore 🙂 but try the view sys.triggers and look for the column modify_date.

You'll probably get some remarks on the quality of this design, but I'll skip that and just try to answer your question.

You could do something like this

SELECT * --...

First thing to check is whether the statistics are updated on your SQL Server 2005 machine.

If they are OK, you should compare the execution plans on both servers (if you...

You could rewrite your query to return 1 row per year (instead of different columns) with one column that holds the year and one that holds the value.

e.g.

col1 col2 col3...