CODEWITHSUNDEEP
sqlsql-server
azharkhan
azharkhan

Reputation: 365

Manually increment a column value in SQL Server database through query when date < currentdate


I have just starting learning database so this would be a rather basic question but I can't figure out how to achieve this.

I have a table in database in which there is a column named Date where I store a date( Just the date in the format dd/mm/yy) and there is another column named IntegerColumn in which initially I am storing the integer value 0.

Question is

How to increment the integer value 0 by 1 in IntegerColumn when the date in Date is less then the current date.

Note that the data type for the column storing the date is nvarchar(50) and I am keeping in check the date format i.e dd/mm/yy through code behind in my asp.net application. And additionally I am using SQL Job Scheduling for the query to recur each time SQL Server finds the date less then the current date.

I have done this much...

DECLARE @IncrementValue int
SET @IncrementValue = 1
UPDATE tableA SET IntegerColumn = IntegerColumn + @IncrementValue
WHERE Date < GETDATE()

Upvotes: 1

Views: 757

Answers (1)

valex
valex

Reputation: 24144

WHY do you store a date value into nvarchar(50) field? That's the issue. In this case you should convert your TEXT field to DATETIME type:

...
WHERE CONVERT(datetime,[Date],3) < GETDATE();

Upvotes: 4

Related Questions