end_date>=getdate()

There are several pieces of data that have begin and end dates in TIMSS. Writing queries to see when that is valid is usually written:

begin_date<=getdate() and end_date is null or end_date>=getdate()

Customer relationships are this type of data. With a query where the end date is greater than or equal to, it implies that if the end date is today, we still want the relationship to be valid. Unfortunately, this will not be the case because time is included with the getdate() function.

The end date is stored in TIMSS without time: 2006-12-31 00:00:00.000, whereas getdate() is returned with time: 2006-12-31 06:48:06.710. When the comparison is done, the time portion makes them never equal, unless the getdate() function was called exactly at midnight.

In my MS SQL database I have a function called TRUNCDATE written by TMAR (there is a similar base function in Oracle). Using this you can remove the time portion of the date so that when the date portion in the comparison is equal, the relationship is still valid.

begin_date<=getdate() and end_date is null or end_date>=dbo.TRUNCDATE(getdate())

If you don't have this function in MS SQL, it's below:

CREATE Function TRUNCDATE(@PDTDATETIME AS DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN CONVERT(DATETIME,(CONVERT(VARCHAR(50),@PDTDATETIME,101)),101)
END

Applies to TIMSS5 & TIMSS6