Function to find latest second of the year for the given date Create FUNCTION [dbo].[EndOfYear] ( @DATE DATETIME ) RETURNS DATETIME AS BEGIN RETURN DATEADD(s, -1, DATEADD(yy, DATEDIFF(yy,0,@DATE)+1,0) ) END