SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 End Date of Time Period Functions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/15/2006 :  19:05:13  Show Profile  Reply with Quote
This script will create and demo 5 functions that return a datetime for the beginning (00:00:00.000) of the last day of a time period relative to the datetime value passed in parameter @DAY.

These functions work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997.

The function names created by this script are:
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 )

This script was tested with SQL Server 2000 only.


I posted a script for End of Week function, F_END_OF_WEEK here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760


This post is a companion to Start of Time Period Functions posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755




/*
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
*/







CODO ERGO SUM

Edited by - Michael Valentine Jones on 04/15/2006 20:39:38
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000