0

I have this table in my Database and i hava a history with entrys:

Tabelname: Activity

ActivityID | DateBegin | DateEnd 1 2013-01-01 2013-01-15 1 2013-01-15 9999-12-31 2 2013-01-20 2013-03-15

Now i want to write a Query which will return the id with the DateEnd = 9999-12-31. In this example i want the return only id 2

I wrote this query, but it doesn't function: (I get a return statement of NULL) 

SELECT ActivityID FROM [dbo].[Activity] where NOT EXISTS (SELECT ActivityID from Activity where DateEnd ='9999-12-31')

Can somebody help me ?

Thanks a lot

Improve this question
2
  • 1
    Why do you use the NOT EXISTS? Commented Nov 21, 2013 at 11:41
  • jus use select * from table where dateend <> '9999-12-31' Commented Nov 21, 2013 at 11:44

3 Answers 3

Reset to default
2

If you want to return all the IDs that don't have any row with date 9999-12-31, you should first check which IDs you don't want to be returned, then avoid them in your query.

So the query becomes:

SELECT ActivityID FROM [dbo].[Activity] WHERE ActivityID NOT IN (SELECT ActivityID from [dbo].[Activity] WHERE DateEnd ='9999-12-31')
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
SELECT ActivityID FROM dbo.Activity As x WHERE NOT EXISTS ( SELECT ActivityID FROM dbo.Activity WHERE DateEnd ='9999-12-31' AND ActivityID = x.ActivityID )
Improve this answer

Comments

0

Please try it

 SELECT ActivityId from Activity where DateEnd != CONVERT(varchar(10), '9999-12-31',101)
Improve this answer

1 Comment

check output of your query

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.