Using DateAdd() to Add 3 days to Every Row in Table

[stanlyn]

Hi,

What is the easiest way to iterate through a MSSQL table and set the value of date2 to whatever the value of date1 is plus 3 days? A sample showing how to use it with the update-set commands along with the where clause. I've googled for some examples with no luck on how to iterate.

Thanks,
Stanley

 

[Mike Lewis]

You don't need to iterate. The UPDATE command will (by default) act on every row in the table:

[tt]UPDATE MyTable SET Date2 = DATEADD(day, 3, Date1)[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

>> You don't need to iterate.
Yes, as sql server is doing it implicitly. What I meant by iterating, implicit or explicit is the process of applying something to all records in scope. It looks as your command does an implicit iteration on all returned rows. This is good news... and simple enough.

>> UPDATE MyTable SET Date2 = DATEADD(day, 3, Date1)

So you are saying this will add 3 days to whatever the value is in date1 for each row based on that specific row's value of date1, such as
for row1 with date1='2020-01-01 12:00:00', date2 will become '2020-04-01 12:00:00' and
for row100 with date1='1996-12-20 14:30:00', date2 will become '1996-12-23 14:30:00' and on and on...
as sql iterates through all the rows returned by the where clause?

And, this would be the same in VFP as...

set filter to (something)
go top
replace date1 with date1+3 all

Thanks,
Stanley

 

[Mike Lewis]

Stanley, your interpretation is correct. I mentioned that UPDATE operates on all rows by default. If you want to apply it to a subset of rows, you would use a WHERE clause.

Your VFP is also correct, except that, rather than setting a filter, you could use a FOR clause (and omit the GO TOP). But note that, in VFP, Date1 must be a Date data type, not a DateTime, otherwise you would be adding three seconds rather than three days. That distinction isn't important in T-SQL.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[stanlyn]

Hi Mike,

The part that I was unclear about using update-set with or without a where clause is how the values from row to row is interpreted as it iterates (implicitly) the selected rows.

In other words, when its doing row with pk=100, does update only look at the other values for only row with pk=100? Then when its dealing with pk=1000, the fields in scope will be only be from the row having pk=1000 and so on, much like the way VFP does things?

A little more clarity about batch updating in sql would be helpful.

Thanks, Stanley

 

[Mike Lewis]

In other words, when its doing row with pk=100, does update only look at the other values for only row with pk=100? Then when its dealing with pk=1000, the fields in scope will be only be from the row having pk=1000 and so on, much like the way VFP does things?

Stanley, I'm not sure I understand what you are saying in the above sentence, although I suspect the answer is yes.

But, you know, there is a very easy way of finding out about this. Just run the code and observe what happens (obviously using test data). It doesn't cost anything to do that. And if you don't have access to SQL Server, you can still experiment with the UPDATE command in VFP. It works in just the same way as in T-SQL, except that, instead of using DATEADD(), you simply add a number to a date field.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Andrzejek]

>set the value of date2 to whatever the value of date1 is plus 3 days
If that is the rule, I would not keep both dates. You just need date1 and a Computed Column for the other date field.


---- Andy

There is a great need for a sarcasm font.

 

[Mike Lewis]

Good point, Andy. I should have spotted that myself.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Andrzejek]

And if that’s the older version of SQL Server, I would use either a view where I can calculate the 3 days difference, or not have date2 in the data base but calculate it any time I need it.
If you do keep date2 there is always a possibility of having the wrong date.



---- Andy

There is a great need for a sarcasm font.

 

[stanlyn]

Hi Andy,

>> >set the value of date2 to whatever the value of date1 is plus 3 days
Yes, the goal is to update column "date2" with the value of column "date1" plus 3 days for each and every row returned by the update's where clause.

>>If that is the rule, I would not keep both dates. You just need date1 and a Computed Column for the other date field.
Why over-complicate this with a computed field? Looks like a single simple update statement would do the trick??? The date2 column is only being used as a target to receive the calculated result.

If a computed field was used for 1000 returned rows, how would I wrap the correct value of the computed field back to the row it pertains to?

What would the statement look like that uses the proposed computed field solution?

Thanks, Stanley

 

[stanlyn]

Hi Andy,

>> And if that’s the older version of SQL Server, I would use either a view where I can calculate the 3 days difference, or not have date2 in the data base but calculate it any time I need it.
>> If you do keep date2 there is always a possibility of having the wrong date.

Yes, you are correct if these date2 is always 3 days added to date1. This is not the case as they have no use or relation to each other and used for two totally different things. I am just creating a baseline for going forward and this command will only be used once for the baseline...

Thanks,
Stanley

 

[stanlyn]

Hi Mike,

>> Stanley, I'm not sure I understand what you are saying in the above sentence, although I suspect the answer is yes.

You had previously stated no iteration is being done. I disagree and here is why...
Lets say the update statement's where clause causes 1000 rows to be returned or in-scope. Now as sqlserver starts its journey by visiting, evaluating and updating each row before traveling onto the next row. To me this is an implicit iteration as the returned list has to be traversed. Do you agree, or what am I missing?

My question was: during sqlserver's journey thru the list evaluating and updating, only the fields for that row are in scope for that row, correct?

I do a lot of testing on my own and when something seems overly complicated or confusing in SQL (or anything else), compared to VFP, I generally reach out for a discussion. Doing so has always been a rewarding experience as others throw in ideas I would have never considered...

Thanks,
Stanley

 

[Andrzejek]

>Why over-complicate this with a computed field?
IMHO, it is the opposite: not over-complicating but (over-?) simplifying.

>What would the statement look like that uses the proposed computed field solution?
You have this in the link provided, something like:
[tt][blue]
ALTER TABLE dbo.MyTableName ADD date2 AS (DATEADD(day, 3, Date1))[/blue]
[/tt]
And that's all what you need to do. Try it, you may like it.

>Looks like a single simple update statement would do the trick???
Yes, but... You will some day end up with data like:

[pre]
date1 date2
1/1/2020 1/4/2020
2/2/2020 2/5/2020
3/3/2020 [red]10/17/1964[/red]
4/4/2020 4/7/2020
[/pre]
And then what? How often do you need to check if all your data is correct?
I would the database worry about it so I sleep easy...


---- Andy

There is a great need for a sarcasm font.

 

[stanlyn]

Hi Mike,

>> I mentioned that UPDATE operates on all rows by default.

Yes, I know that, but my question was about how update would use values from the same row when evaluated. I know that an:
Update table set status = 'OK' where pk < 1000
would assign 'OK' to all pks less that 1000 - simple enough...

My discussion is more about using a dynamic field value that could be different between rows, and changing a different field's value in the same row, based on a value from a different field in the same row. Notice the use of "same row" for both the source, (fields to evaluate) and destination, (fields to update).

This has been so hard to convey to the forum. I hope this explains it best.

Thanks,
Stanley

 

[stanlyn]

Hi Andy,

>> Yes, but... You will some day end up with data like:

And that is expected as there is no relationship going forward between the two, as I'm now only establishing a baseline. And yes, I understand what you are saying and agree.

>> ALTER TABLE dbo.MyTableName ADD date2 AS (DATEADD(day, 3, Date1))

How could this possibly apply to a subset of rows, which is why I'm using a where clause? Doesn't 'Alter table' only apply to all rows. Some of the rows have a date2 value that needs kept and the alter table command would not work, (as I see it), correct?

I will try out and explore the statement you wrote and it looks much more complicated with the 'ADD' keyword and looks like you are creating a new field named 'date2" (I guess), when the table already has a field named 'date2'. Simple to me is:

Update dbo.MyTableName set date2 = DATEADD(day, 3, Date1) where state = 'KY'

Thanks again,
Stanley

 

[Mike Lewis]

Stanley,

You've raised several points which I will try to answer in due course. But let me just pick up on your latest post, where you question the use of [tt]ALTER TABLE dbo.MyTableName ADD date2 AS (DATEADD(day, 3, Date1))[/tt] in conjunction with a need to filter the result set.

The point about that ALTER TABLE is that it is not physically adding a column to the table. Rather, it is creating a computed column, which means that the value is determined on the fly whenever it is needed. So, if at some later time, you did something like [tt]SELECT Date2 FROM MyTable[/tt] or [tt]SELECT * FROM MyTable WHERE Date2 = xxx[/tt], SQL Server would determine the value of Date2 (by adding three days to Date1) at that point.

In fact, you can achieve exactly the same effect by specifying the calculation within the query:

[tt]SELECT DATEADD(day, 3, Date1) AS Date2 FROM MyTable[/tt]

or

[tt]SELECT * FROM MyTable WHERE DATEADD(day, 3, Date1)= xxx[/tt]

but that would have the disadvantage that the details of the calculation would be scattered around your code and therefore difficult to alter, for example if you later needed to change the number of days being added. By using a computed column, you localise that information to your ALTER TABLE statement.

I hope this makes sense.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Andrzejek]

Stanley, I guess you've never looked at the link I provided. That's why the confusion.

I've assumed (and that was wrong on my part) you do not have a field date2 in your table, that's where only this one statement is needed. And yes, it adds a field date2 to your table.

If you already do have date2 field, it needs to be removed and added as a Computed Column.
In the link, there is information about how "To change an existing column to a computed column" at the bottom.


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

>how update would use values from the same row when evaluated

The best way to see it is to do it.

If you have data in your table like:
(It actually does not matter what is in Date2 field since you want to change it anyway)

[pre]
Date1 Date2
1/1/2020 null
2/2/2020 null
3/3/2020 null
4/4/2020 null
[/pre]
and run the Update Mike gave you:
[tt]
UPDATE MyTable SET Date2 = DATEADD(day, 3, Date1)
[/tt]
and SEE what happens with your data in Date2 field. It all will be clear.
Try it and report back


---- Andy

There is a great need for a sarcasm font.

 

[stanlyn]

Hi Andy,

>> Stanley, I guess you've never looked at the link I provided. That's why the confusion.

And you would be wrong... Any time someone provides links, I always explore them. Which is why I wrote you back with my observation. Mike's answer is suggesting a way it works that while looking at the code make no sense of it doing what he says it does. I do not see how

"ALTER TABLE dbo.MyTableName ADD date2 AS (DATEADD(day, 3, Date1))"

could possibly work, no logical sense...

In using the "as" keyword in one of Mike's samples, I have always know that it creates an alias, but nowhere is it being assigned to the real field in Mike's sample as there is no update or
"=" assignments. Is something here implicitly applied. I always thought we use "select" to select rows, "update" to update/change them.

My initial question involves tables where I need to find date1 in table2 and update date2 in table1, hence the reason for my embedded sub select, and not sure how we go to here. Anyway, worth it.

Thanks,
Stanley

 

[stanlyn]

Hi Andy,

>> UPDATE MyTable SET Date2 = DATEADD(day, 3, Date1)
>> and SEE what happens with your data in Date2 field.

This is clear, logically makes sense and how I would write it.

What doesn't make sense is changing the value with a select statement that has an "as" keyword with no assignment as previously mentioned.

Thanks,
Stanley