DATE QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DATE QUERY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-14 : 08:05:21

PREETHA writes "I have a couple of dates in a field in a table. I want to get the dates which are not in the table and which are between 2 calendar days. How can I accomplish this? I tried 'NOT IN' but didn't give me any result.Please help.
Preetha"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 08:11:30

>>I want to get the dates which are not in the table

Do you use any other tables?
I think you can have other table called DateTable which has all dates for the calendar year do query

Select DateCol from DateTable T where not exists(Select DateCol from yourTable where DateCol=T.DateCol)

Madhivanan

Failing to plan is Planning to fail

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-14 : 11:53:35

You need a table of sequential values. You can create it on the fly, by these are so handy that I usually create them as a permanent table:

CREATE TABLE [dbo].[SequentialNumbers] ([SeqValue] [int] NOT NULL)

Populate it with, say 100 integers, starting with 0.

Then run a script like this to get the results you want:

select	dateadd(day, SeqValue, [CalendarBeginDate])
from	SequentialNumbers
	left outer join [YourTable] on dateadd(day, SeqValue, @BeginDate) = [YourTable].[YourDate]
where	dateadd(day, SeqValue, @BeginDate) <= CalendarEndDate
	and [YourTable].[YourDate] is null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources