Manually increment a column value in SQL Server database through query when date < currentdate
-
Hi.
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 interger 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() -
Hi.
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 interger 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()I would recommend that you change the datatype of your date column to datetime - that's what it's there fore! Also, give your column a more meaningful name then Date - that is a reserved word and may cause you trouble in the future.
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Hi.
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 interger 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()If you are using SQL Server, you don't need to run a job to do this - you can have a computed column. Create a column Called Days (or whatever you want to call it). Do not set a data type. In the computed column formula enter datediff(d,[DateColumn], Getdate()) - this calculates the number of days between the [DateColumn] and teh current date
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
I would recommend that you change the datatype of your date column to datetime - that's what it's there fore! Also, give your column a more meaningful name then Date - that is a reserved word and may cause you trouble in the future.
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
Chris thanks for the reply. Chris I infact changed the datatype to date and additionally my column name is actually ReturnDate not Date, I had chose just a random name. But still when I execute the query it does not increment the value in Column. Any more suggesstions ?
-
Hi.
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 interger 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()You avoided a nasty situation (and comment) by changing to date format in your database, ALWAYS store date in the correct format. If you go down the path you describe, your are going to have to run the query EVERY day. As Chris suggested use a virtual computed column, I would use a view for this. Do not store the value, a view will automatically calc every time you access it.
Never underestimate the power of human stupidity RAH
-
You avoided a nasty situation (and comment) by changing to date format in your database, ALWAYS store date in the correct format. If you go down the path you describe, your are going to have to run the query EVERY day. As Chris suggested use a virtual computed column, I would use a view for this. Do not store the value, a view will automatically calc every time you access it.
Never underestimate the power of human stupidity RAH
Thanks RAH for the reply. Firstly I agree with the format thing. Secondly I want the increment to be totally automatic and should occur without any interference( i mean without accessing it ). I hope I understood you right in this because I have never used a virtual computed column or a view before. Do the ones you have suggested do it automatically. If they do so i'll surely have a try.
-
Thanks RAH for the reply. Firstly I agree with the format thing. Secondly I want the increment to be totally automatic and should occur without any interference( i mean without accessing it ). I hope I understood you right in this because I have never used a virtual computed column or a view before. Do the ones you have suggested do it automatically. If they do so i'll surely have a try.
As I said earlier, using a computed column you do not need a job to run at all
========================================================= I'm an optoholic - my glass is always half full of vodka. =========================================================
-
Thanks RAH for the reply. Firstly I agree with the format thing. Secondly I want the increment to be totally automatic and should occur without any interference( i mean without accessing it ). I hope I understood you right in this because I have never used a virtual computed column or a view before. Do the ones you have suggested do it automatically. If they do so i'll surely have a try.
hilbiazhar wrote:
I want the increment to be totally automatic
Title claims otherwise.
hilbiazhar wrote:
should occur without any interference( i mean without accessing it
A computed column would be calculated when it's value is requested. Why should it be without any interference? If you don't look at the number, then what is the use of incrementing it in the first place?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
hilbiazhar wrote:
I want the increment to be totally automatic
Title claims otherwise.
hilbiazhar wrote:
should occur without any interference( i mean without accessing it
A computed column would be calculated when it's value is requested. Why should it be without any interference? If you don't look at the number, then what is the use of incrementing it in the first place?
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Oh i got it now. Actually I had not understood it properly. Thanks for making it clear anyway. Definitely this would be a good approach than keeping SQL Job Schedules like you said.