I understand what setvalidtime does, but how is gotodate different from it? I read the documentation but I am still confused with how gotodate works specifically and it is different from setvalidtime. Please help!
GotoDate gives you a view of the data at a specific time in the past. It is based on the retiretime,createtime of the versioned rows. These values are populated by Workspace Manager with the system time when the DML occurs. Which rows are being returned will be dependent on the history mode(NONE, VIEW_W_OVERWRITE, VIEW_WO_OVERWRITE) of the table. So, for example, if you have a table that a row with id=1 was inserted into the table on Oct. 23, 2012, and you then specified Oct. 1 2012 for the GotoDate procedure and queried the table, you would not see the row. The same principle applies for updates and deletes.
SetValidTime uses a time range and is only applicable to validtime tables. It returns the rows that have an overlap with the specified time range and the range contained in the wm_valid column that is added to the version enabled table. This will only consider current, non-historical records, unless used in conjunction with GotoDate. The wm_valid values are populated by the user or a default value if none is explicitly specified.