How to not update ValidFrom - ValidTo table field?

Question

I have to update a record on Table with ValidTimeStateFieldType UTCDateTime, but I need to Update only a string field. If I use this code:

while select forUpdate MyTable
{
  MyTable.StringField = "Test";
  MyTable_UPD.validTimeStateUpdateMode(ValidTimeStateUpdate::EffectiveBased);

  ttsbegin;
  MyTable.update();
  ttscommit;
}

After command MyTable.update(); change the value on table MyTable.ValidFrom or MyTable.ValidTo. I need to change only the value on StringField, I don't need to change the values on ValidFrom/To. I want to preserve the same information on UTC field and I don't want to create a new record.

If I use MyTable.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction); I receive an error.

It's possible to change only value and don't touch anything on ValueFrom/To field?

Thanks,

enjoy!

Answer 1

If I am not mistaken it is not possible to modify only your StringField because in your table in Indexes you have defined DateTime field.

You need use validTimeStateUpdateMode function, in your code use MyTable and then MyTable.update() but you use validTimeStateUpdateMode(ValidTimeStateUpdate::EffectiveBased); in MyTable_UPD not MyTable.

Try This:

ttsbegin;
while select forUpdate MyTable
{
    MyTable.StringField = "Test";
    MyTable.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
    MyTable.update();

}
ttscommt;

Answer 2

Try using a valid time state range in your select statement.

Something like this:

MyTable.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);

ttsbegin;
while select forUpdate validTimeState(1\1\1900, 31\12\2154) * from MyTable
{
  MyTable.StringField = "Test";
  MyTable.update();
}
ttscommit;

I used examples from this MSDN page for help: https://msdn.microsoft.com/en-us/library/gg843767.aspx