To get a snapshot of a valid-time table where the valid-time period in the result rows overlap a specific time, use the VALIDTIME AS OF temporal qualifier in the SELECT statement. For example:
VALIDTIME AS OF DATE '2005-03-14' SELECT * FROM Policy;
Result:
Policy_ID Customer_ID Policy_Type Policy_Details --------- ----------- ----------- ------------------- 497201 304779902 AU STD-CH-524-WXY-00
AS OF also accepts business calendar functions as date input. This request returns all policies that were valid on January 1, 2011. For more information on the business calendar functions, see Teradata Vantage™ - SQL Date and Time Functions and Expressions, B035-1211.
VALIDTIME AS OF TD_YEAR_BEGIN(DATE ‘2011-05-24’) SELECT * FROM Policy;
Result:
Policy_ID Customer_ID Policy_Type Policy_Details ----------- ----------- ----------- -------------------- 541008 246824626 AP STD-CH-345-NXY-00 540944 123344567 HM STD-PL-332-YXY-00 497201 304779902 AU STD-CH-524-WXY-02 541077 766492008 HP STD-CH-344_YXY-00