1

Using SQL Server 2005

When i insert the date it should compare the date in the table.

If it is equal with other date, it should display a error message and also it should allow only to insert the next date.

For Example

Table1

Date 20091201 20091202 Insert into table1 values('20091202')

The above query should not allow to insert the same value

Insert into table1 values('20091204')

The above query also should not allow to insert the long gap date.

The query should allow only the next date. It should not allow same date and long gap date.

How to insert a query with this condition.

Is Possible in SQL or VB.Net

Need SQL Query or VB.Net code Help

Improve this question
2
  • 4
    Not clear what you want, are you trying to validate input or do you want an "auto-increment" date as a column on a table? Commented Feb 7, 2010 at 14:03
  • Yes I want to validate the Input, if it is equal or greater then (means it should allow only next date). It Should not all long gap dates. Commented Feb 8, 2010 at 5:31

3 Answers 3

Reset to default
1

You could use a where clause to ensure that the previous day is present in the table, and the current day is not:

insert into table1 ([dateColumn]) select '20091204' where exists ( select * from table1 where [dateColumn] = dateadd(d,-1,'20091204') ) and not exists ( select * from table1 where [dateColumn] = '20091204' ) if @@rowcount <> 1 raiserror ('Oops', 16, 1)

If the insert succeeds, @@rowcount will be set to 1. Otherwise, an error is returned to VB using raiserror.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

How does this prevent the current date from being inserted twice? I guess you're assuming there is already a unique constraint on the table (though the question seemed to imply that there isn't).
@Aaron Bertrand: Right, I'll add an extra check for that, a unique constraint would work too
A unique constraint is better because it is tough to ensure that everyone inserting into this table uses this specific query to do so.
1

Why not just have a table of dates set up in advance, and update a row once you want to "insert" that date?

I'm not sure I understand the point of inserting a new date only once, and never allowing a gap. Could you describe your business problem in a little more detail?

Of course you could use an IDENTITY column, and then have a computed column or a view that calculates the date from the number of days since (some date). But IDENTITY columns do not guarantee contiguity, nor do they even guarantee uniqueness on their own (unless you set up suc a constraint separately).

Preventing duplicates should be done at the table level with a unique constraint, not with a query. You can check for duplicates first so that you can handle errors in your own way (rather than let the engine raise an exception for you), but that shouldn't be your only check.

Improve this answer

Comments

0

Sounds like your date field should just be unique with auto-increment.

Improve this answer

2 Comments

The SQL Server equivalent of auto-increment is identity, and as far as I know, it only supports discrete numeric types
@Andomar: An auto-incrementing date field would be silly indeed. What meaning would the dates have?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.