/*
Functions created by this script:
	dbo.F_END_OF_CENTURY( @DAY )
	dbo.F_END_OF_DECADE( @DAY )
	dbo.F_END_OF_YEAR( @DAY )
	dbo.F_END_OF_QUARTER( @DAY )
	dbo.F_END_OF_MONTH( @DAY )

*/
go
if objectproperty(object_id('dbo.F_END_OF_CENTURY'),'IsScalarFunction') = 1
	begin drop function dbo.F_END_OF_CENTURY end
go
create function dbo.F_END_OF_CENTURY
	( @DAY datetime )
returns  datetime
as
/*
Function: F_END_OF_CENTURY
	Finds start of last day of century at 00:00:00.000
	for input datetime, @DAY.
	Valid for all SQL Server datetimes
*/
begin

return   dateadd(yy,99-(year(@day)%100),dateadd(yy,datediff(yy,-1,@DAY),-1))

end
go
if objectproperty(object_id('dbo.F_END_OF_DECADE'),'IsScalarFunction') = 1
	begin drop function dbo.F_END_OF_DECADE end
go
create function dbo.F_END_OF_DECADE
	( @DAY datetime )
returns  datetime
as
/*
Function: F_END_OF_DECADE
	Finds start of last day of decade at 00:00:00.000
	for input datetime, @DAY.
	Valid for all SQL Server datetimes
*/
begin

return   dateadd(yy,9-(year(@day)%10),dateadd(yy,datediff(yy,-1,@DAY),-1))

end
go
if objectproperty(object_id('dbo.F_END_OF_YEAR'),'IsScalarFunction') = 1
	begin drop function dbo.F_END_OF_YEAR end
go
create function dbo.F_END_OF_YEAR
	( @DAY datetime )
returns  datetime
as
/*
Function: F_END_OF_YEAR
	Finds start of last day of year at 00:00:00.000
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return  dateadd(yy,datediff(yy,-1,@DAY),-1)

end
go
if objectproperty(object_id('dbo.F_END_OF_QUARTER'),'IsScalarFunction') = 1
	begin drop function dbo.F_END_OF_QUARTER end
go
create function dbo.F_END_OF_QUARTER
	( @DAY datetime )
returns  datetime
as
/*
Function: F_END_OF_QUARTER
	Finds start of last day of quarter at 00:00:00.000
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return   dateadd(qq,datediff(qq,-1,@DAY),-1)

end
go
if objectproperty(object_id('dbo.F_END_OF_MONTH'),'IsScalarFunction') = 1
	begin drop function dbo.F_END_OF_MONTH end
go
create function dbo.F_END_OF_MONTH
	( @DAY datetime )
returns  datetime
as
/*
Function: F_END_OF_MONTH
	Finds start of last day of month at 00:00:00.000
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return  dateadd(mm,datediff(mm,-1,@DAY),-1)

end
go
/*
Start of test script

Load dates to test F_END_OF functions
*/

declare @test_dates table ( DT datetime not null primary key clustered )

declare @year varchar(4)
select @year = convert(varchar(4),year(getdate()))

insert into @test_dates (DT)
select DT = getdate()	union all
select  '17530101 00:00:00.000'	union all
-- Test start of Decade and Century
select  '17591231 23:59:59.997'	union all
select  '17600101 00:00:00.000'	union all
select  '17991231 23:59:59.997'	union all
select  '18000101 00:00:00.000'	union all
select  '19000101 00:00:00.000'	union all
select  '19001231 23:59:59.997'	union all
select  '19400101 00:00:00.000'	union all
select  '19491231 23:59:59.997'	union all
select  '19900101 00:00:00.000'	union all
select  '19991231 23:59:59.997'	union all
-- For start of Month, Quarter, and Year testing
select @year+'0101 00:00:00.000'	union all
select @year+'0131 23:59:59.997'	union all
select @year+'0201 00:00:00.000'	union all
select @year+'0228 23:59:59.997'	union all
select @year+'0301 00:00:00.000'	union all
select @year+'0331 23:59:59.997'	union all
select @year+'0401 00:00:00.000'	union all
select @year+'0430 23:59:59.997'	union all
select @year+'0501 00:00:00.000'	union all
select @year+'0531 23:59:59.997'	union all
select @year+'0601 00:00:00.000'	union all
select @year+'0630 23:59:59.997'	union all
select @year+'0701 00:00:00.000'	union all
select @year+'0731 23:59:59.997'	union all
select @year+'0801 00:00:00.000'	union all
select @year+'0831 23:59:59.997'	union all
select @year+'0901 00:00:00.000'	union all
select @year+'0930 23:59:59.997'	union all
select @year+'1001 00:00:00.000'	union all
select @year+'1031 23:59:59.997'	union all
select @year+'1101 00:00:00.000'	union all
select @year+'1130 23:59:59.997'	union all
select @year+'1201 00:00:00.000'	union all
select @year+'1231 23:59:59.997'	union all
select  '89000101 00:00:00.000'	union all
select  '89991231 23:59:59.997'	union all
select  '99000101 00:00:00.000'	union all
select  '99991231 23:59:59.997'
order by
	1

select
	TYPE = 'CENTURY' ,
	DT = convert(varchar(23),DT,121),
	FUNCTION_RESULT =
		convert(varchar(23),dbo.F_END_OF_CENTURY( DT ),121)
from
	 @test_dates
order by
	DT

select
	TYPE = 'DECADE' ,
	DT = convert(varchar(23),DT,121),
	FUNCTION_RESULT =
		convert(varchar(23),dbo.F_END_OF_DECADE( DT ),121)
from
	 @test_dates
order by
	DT

select
	TYPE = 'YEAR' ,
	DT = convert(varchar(23),DT,121),
	FUNCTION_RESULT =
		convert(varchar(23),dbo.F_END_OF_YEAR( DT ),121)
from
	 @test_dates
order by
	DT

select
	TYPE = 'QUARTER' ,
	DT = convert(varchar(23),DT,121),
	FUNCTION_RESULT =
		convert(varchar(23),dbo.F_END_OF_QUARTER( DT ),121)
from
	 @test_dates
order by
	DT

select
	TYPE = 'MONTH' ,
	DT = convert(varchar(23),DT,121),
	FUNCTION_RESULT =
		convert(varchar(23),dbo.F_END_OF_MONTH( DT ),121)
from
	 @test_dates
order by
	DT

/*
End of test script
*/