Start of Time Period Functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Start of Time Period Functions

Author

Topic

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-15 : 14:28:46

There are a lot of questions posted on SQLTEAM asking how to find the beginning of various time periods. The script will create and demo 14 functions that return a datetime for the beginning of a time period relative to the datetime value passed in parameter @DAY.

I put together this script to create these functions for several reasons:
1. To allow people to find them on their own without having to post a question.
2. To allow posted questions to be answered with a reference to this script.
3. To document algorithms that work for the widest possible range of datetime values. All except for the Century and Decade functions work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997. The Century is limited to datetimes from 1800/01/01 forward, because 1700/01/01 is not valid in SQL Server. The Decade function is limited to datetimes from 1760/01/01 forward, because 1750/01/01 is not valid in SQL Server.
4. And last, you can actually use them on your application.

The function names created by this script are:
dbo.F_START_OF_CENTURY( @DAY )
dbo.F_START_OF_DECADE( @DAY )
dbo.F_START_OF_YEAR( @DAY )
dbo.F_START_OF_QUARTER( @DAY )
dbo.F_START_OF_MONTH( @DAY )
dbo.F_START_OF_DAY( @DAY )
dbo.F_START_OF_HOUR( @DAY )
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )
dbo.F_START_OF_X_MIN( @DAY )
dbo.F_START_OF_MINUTE( @DAY )
dbo.F_START_OF_SECOND( @DAY )

There is a separate post for function dbo.F_START_OF_WEEK to find the first day of the week at this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

This script was tested with SQL Server 2000 only.

I posted a script for End Date of Time Period Functions here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759

Other Date/Time Info and Script Links:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

Edit 2006-11-01:
Added dbo.F_START_OF_X_MIN( @DAY ) at the suggestion of Peter.

Edit 2007-02-24:
Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code:
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )

Edit 2009-03-24:
Added script at end to demo start of time period methods using in-line code, along with output from sample script.




/*
Functions created by this script:
	dbo.F_START_OF_CENTURY( @DAY )
	dbo.F_START_OF_DECADE( @DAY )
	dbo.F_START_OF_YEAR( @DAY )
	dbo.F_START_OF_QUARTER( @DAY )
	dbo.F_START_OF_MONTH( @DAY )
	dbo.F_START_OF_DAY( @DAY )
	dbo.F_START_OF_HOUR( @DAY )
	dbo.F_START_OF_30_MIN( @DAY )
	dbo.F_START_OF_20_MIN( @DAY )
	dbo.F_START_OF_15_MIN( @DAY )
	dbo.F_START_OF_10_MIN( @DAY )
	dbo.F_START_OF_05_MIN( @DAY )
	dbo.F_START_OF_MINUTE( @DAY )
	dbo.F_START_OF_SECOND( @DAY )

*/
go
if objectproperty(object_id('dbo.F_START_OF_CENTURY'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_CENTURY end
go
create function dbo.F_START_OF_CENTURY
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_CENTURY
	Finds start of first day of century at 00:00:00.000
	for input datetime, @DAY.
	Valid for all SQL Server datetimes >= 1800-01-01 00:00:00.000
	Returns null if @DAY < 1800-01-01 00:00:00.000
*/
begin

declare @BASE_DAY datetime
select  @BASE_DAY = '18000101'

IF @DAY < @BASE_DAY return null 

return   dateadd(yy,(datediff(yy,@BASE_DAY,@DAY)/100)*100,@BASE_DAY)	

end
go
if objectproperty(object_id('dbo.F_START_OF_DECADE'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_DECADE end
go
create function dbo.F_START_OF_DECADE
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_DECADE
	Finds start of first day of decade at 00:00:00.000
	for input datetime, @DAY.
	Valid for all SQL Server datetimes >= 1760-01-01 00:00:00.000
	Returns null if @DAY < 1760-01-01 00:00:00.000
*/
begin

declare @BASE_DAY datetime
select  @BASE_DAY = '17600101'

IF @DAY < @BASE_DAY return null 

return   dateadd(yy,(datediff(yy,@BASE_DAY,@DAY)/10)*10,@BASE_DAY)	

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

return  dateadd(yy,datediff(yy,0,@DAY),0)

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

return   dateadd(qq,datediff(qq,0,@DAY),0)

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

return  dateadd(mm,datediff(mm,0,@DAY),0)

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

return  dateadd(dd,datediff(dd,0,@DAY),0)

end
go
if objectproperty(object_id('dbo.F_START_OF_HOUR'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_HOUR end
go
create function dbo.F_START_OF_HOUR
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_HOUR
	Finds beginning of hour
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return   dateadd(hh,datediff(hh,0,@DAY),0)

end
go
if objectproperty(object_id('dbo.F_START_OF_30_MIN'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_30_MIN end
go
create function dbo.F_START_OF_30_MIN
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_30_MIN
	Finds beginning of 30 minute period
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return  dateadd(mi,(datepart(mi,@Day)/30)*30,dateadd(hh,datediff(hh,0,@Day),0))

end
go
if objectproperty(object_id('dbo.F_START_OF_20_MIN'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_20_MIN end
go
create function dbo.F_START_OF_20_MIN
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_20_MIN
	Finds beginning of 20 minute period
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return  dateadd(mi,(datepart(mi,@Day)/20)*20,dateadd(hh,datediff(hh,0,@Day),0))

end
go
if objectproperty(object_id('dbo.F_START_OF_15_MIN'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_15_MIN end
go
create function dbo.F_START_OF_15_MIN
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_15_MIN
	Finds beginning of 15 minute period
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return  dateadd(mi,(datepart(mi,@Day)/15)*15,dateadd(hh,datediff(hh,0,@Day),0))

end
go
if objectproperty(object_id('dbo.F_START_OF_10_MIN'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_10_MIN end
go
create function dbo.F_START_OF_10_MIN
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_10_MIN
	Finds beginning of 10 minute period
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return  dateadd(mi,(datepart(mi,@Day)/10)*10,dateadd(hh,datediff(hh,0,@Day),0))

end
go
if objectproperty(object_id('dbo.F_START_OF_05_MIN'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_05_MIN end
go
create function dbo.F_START_OF_05_MIN
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_05_MIN
	Finds beginning of 5 minute period
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return  dateadd(mi,(datepart(mi,@Day)/5)*5,dateadd(hh,datediff(hh,0,@Day),0))

end
go
if objectproperty(object_id('dbo.F_START_OF_X_MIN'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_X_MIN end
go
create function dbo.F_START_OF_X_MIN
	(
	@DAY datetime,
	@INTERVAL int
	)
returns  datetime
as
/*
Function: F_START_OF_X_MIN
	Finds beginning of @INTERVAL minute period
	for input datetime, @DAY.
	If @INTERVAL = zero, returns @DAY.
	Valid for all SQL Server datetimes.
*/
begin

-- Prevent divide by zero error
if @INTERVAL = 0 return @DAY

declare @BASE_DAY datetime
set  @BASE_DAY = dateadd(dd,datediff(dd,0,@Day),0)

return  dateadd(mi,(datediff(mi,@BASE_DAY,@Day)/@INTERVAL)*@INTERVAL,@BASE_DAY)

end
go
if objectproperty(object_id('dbo.F_START_OF_MINUTE'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_MINUTE end
go
create function dbo.F_START_OF_MINUTE
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_MINUTE
	Finds beginning of minute
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return   dateadd(ms,-(datepart(ss,@DAY)*1000)-datepart(ms,@DAY),@DAY)

end
go
if objectproperty(object_id('dbo.F_START_OF_SECOND'),'IsScalarFunction') = 1
	begin drop function dbo.F_START_OF_SECOND end
go
create function dbo.F_START_OF_SECOND
	( @DAY datetime )
returns  datetime
as
/*
Function: F_START_OF_SECOND
	Finds beginning of second
	for input datetime, @DAY.
	Valid for all SQL Server datetimes.
*/
begin

return   dateadd(ms,-datepart(ms,@DAY),@DAY)

end
go

/*
Start of test script

Load dates to test F_START_OF functions

*/

declare @test_dates table ( DT datetime not null primary key clustered )
declare @today varchar(10)
select @today = convert(varchar(10),getdate(),112)
declare @year varchar(4)
select @year = convert(varchar(4),year(getdate()))
declare @lyear varchar(10)
select @lyear = convert(varchar(10),getdate()-345,112)

insert into @test_dates (DT)
select DT = getdate()	union all
select  '17530101 00:00:00.000'	union all
-- Test start of Decade cutoff
select  '17591231 23:59:59.997'	union all
select  '17600101 23:04:59.997'	union all
-- Test start of Century cutoff
select  '17991231 23:59:59.997'	union all
select  '18000101 00:00:00.000'	union all
-- Test start of Decade and Century
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 Hour testing
select  @lyear+' 00:00:00.000'	union all
select  @lyear+' 00:59:59.997'	union all
select  @lyear+' 01:00:00.000'	union all
select  @lyear+' 01:59:59.997'	union all
select  @lyear+' 12:00:00.000'	union all
select  @lyear+' 12:59:59.997'	union all
select  @lyear+' 17:00:00.000'	union all
select  @lyear+' 17:59:59.997'	union all
select  @lyear+' 23:00:00.000'	union all
select  @lyear+' 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
-- Test start of 5, 10, 15, 20, and 30 min testing 
select  @today+' 23:04:59.997'	union all
select  @today+' 23:09:59.997'	union all
select  @today+' 23:14:59.997'	union all
select  @today+' 23:19:59.997'	union all
select  @today+' 23:24:59.997'	union all
select  @today+' 23:29:59.997'	union all
select  @today+' 23:34:59.997'	union all
select  @today+' 23:39:59.997'	union all
select  @today+' 23:44:59.997'	union all
select  @today+' 23:49:59.997'	union all
select  @today+' 23:54:59.997'	union all
select  @today+' 23:59:59.997'	union all
select   '99991231 23:59:59.997'
order by
	1

-- Convert dates in @test_dates table to test F_START_OF functions

select
	TYPE = 'CENTURY' ,
	DT = convert(varchar(23),DT,121),
	FUNCTION_RESULT =
		convert(varchar(23),dbo.F_START_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_START_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_START_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_START_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_START_OF_MONTH( DT ),121)
from
	 @test_dates
order by
	DT

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

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


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

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

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

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

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

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

select
	TYPE = 'SECOND' ,
	DT = convert(varchar(23),DT,121),
	FUNCTION_RESULT =
		convert(varchar(23),dbo.F_START_OF_SECOND( DT ),121)
from
	 @test_dates
order by
	DT
/*
End of test script
*/


/*
Script to demo Start of Time period methods in-line for various types of time periods.
All methods will run with no error for any valid datetime value
*/

set nocount on
declare @day table (DT datetime not null primary key clustered)

-- Load Test Dates
insert into @day (DT)
select DT = getdate()
union all
select '2014-05-24 16:56:27.667'
union all
select '17530101 00:00:00.000'
union all
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 '99991231 23:59:59.997'
order by 1

select Test_Dates = DT  from @day order by 1

select	START_OF_CENTURY = 
	case
	when a.DT < '18000101'
	then convert(datetime,null)
	else dateadd(yy,(datediff(yy,'18000101',a.DT)/100)*100,'18000101')
	end
from @day a

select	START_OF_DECADE = 
	case
	when a.DT < '17600101'
	then convert(datetime,null)
	else dateadd(yy,(datediff(yy,'17600101',a.DT)/10)*10,'17600101')
	end
from @day a

select START_OF_YEAR = dateadd(yy,datediff(yy,0,a.DT),0) from @day a

select START_OF_QUARTER = dateadd(qq,datediff(qq,0,a.DT),0) from @day a

select START_OF_MONTH = dateadd(mm,datediff(mm,0,a.DT),0) from @day a

select START_OF_DAY  = dateadd(dd,datediff(dd,0,a.DT),0) from @day a

select START_OF_HOUR = dateadd(hh,datediff(hh,0,a.DT),0) from @day a

select START_OF_30_MIN = dateadd(mi,(datepart(mi,a.DT)/30)*30,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a

select START_OF_20_MIN = dateadd(mi,(datepart(mi,a.DT)/20)*20,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a

select START_OF_15_MIN = dateadd(mi,(datepart(mi,a.DT)/15)*15,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a

select START_OF_10_MIN = dateadd(mi,(datepart(mi,a.DT)/10)*10,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a

select START_OF_05_MIN = dateadd(mi,(datepart(mi,a.DT)/5)*5,dateadd(hh,datediff(hh,0,a.DT),0)) from @day a

select START_OF_MINUTE = dateadd(ms,-(datepart(ss,a.DT)*1000)-datepart(ms,a.DT),a.DT) from @day a

select START_OF_SECOND = dateadd(ms,-datepart(ms,a.DT),a.DT) from @day a

Results for in-line code demo:


Test_Dates                                             
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:59:59.997
1760-01-01 00:00:00.000
1799-12-31 23:59:59.997
1800-01-01 00:00:00.000
2009-03-24 17:03:53.717
2014-05-24 16:56:27.667
9999-12-31 23:59:59.997

START_OF_CENTURY                                       
------------------------------------------------------ 
NULL
NULL
NULL
NULL
1800-01-01 00:00:00.000
2000-01-01 00:00:00.000
2000-01-01 00:00:00.000
9900-01-01 00:00:00.000

START_OF_DECADE                                        
------------------------------------------------------ 
NULL
NULL
1760-01-01 00:00:00.000
1790-01-01 00:00:00.000
1800-01-01 00:00:00.000
2000-01-01 00:00:00.000
2010-01-01 00:00:00.000
9990-01-01 00:00:00.000

START_OF_YEAR                                          
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-01-01 00:00:00.000
1760-01-01 00:00:00.000
1799-01-01 00:00:00.000
1800-01-01 00:00:00.000
2009-01-01 00:00:00.000
2014-01-01 00:00:00.000
9999-01-01 00:00:00.000

START_OF_QUARTER                                       
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-10-01 00:00:00.000
1760-01-01 00:00:00.000
1799-10-01 00:00:00.000
1800-01-01 00:00:00.000
2009-01-01 00:00:00.000
2014-04-01 00:00:00.000
9999-10-01 00:00:00.000

START_OF_MONTH                                         
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-01 00:00:00.000
1760-01-01 00:00:00.000
1799-12-01 00:00:00.000
1800-01-01 00:00:00.000
2009-03-01 00:00:00.000
2014-05-01 00:00:00.000
9999-12-01 00:00:00.000

START_OF_DAY                                           
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 00:00:00.000
1760-01-01 00:00:00.000
1799-12-31 00:00:00.000
1800-01-01 00:00:00.000
2009-03-24 00:00:00.000
2014-05-24 00:00:00.000
9999-12-31 00:00:00.000

START_OF_HOUR                                          
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:00:00.000
1760-01-01 00:00:00.000
1799-12-31 23:00:00.000
1800-01-01 00:00:00.000
2009-03-24 17:00:00.000
2014-05-24 16:00:00.000
9999-12-31 23:00:00.000

START_OF_30_MIN                                        
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:30:00.000
1760-01-01 00:00:00.000
1799-12-31 23:30:00.000
1800-01-01 00:00:00.000
2009-03-24 17:00:00.000
2014-05-24 16:30:00.000
9999-12-31 23:30:00.000

START_OF_20_MIN                                        
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:40:00.000
1760-01-01 00:00:00.000
1799-12-31 23:40:00.000
1800-01-01 00:00:00.000
2009-03-24 17:00:00.000
2014-05-24 16:40:00.000
9999-12-31 23:40:00.000

START_OF_15_MIN                                        
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:45:00.000
1760-01-01 00:00:00.000
1799-12-31 23:45:00.000
1800-01-01 00:00:00.000
2009-03-24 17:00:00.000
2014-05-24 16:45:00.000
9999-12-31 23:45:00.000

START_OF_10_MIN                                        
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:50:00.000
1760-01-01 00:00:00.000
1799-12-31 23:50:00.000
1800-01-01 00:00:00.000
2009-03-24 17:00:00.000
2014-05-24 16:50:00.000
9999-12-31 23:50:00.000

START_OF_05_MIN                                        
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:55:00.000
1760-01-01 00:00:00.000
1799-12-31 23:55:00.000
1800-01-01 00:00:00.000
2009-03-24 17:00:00.000
2014-05-24 16:55:00.000
9999-12-31 23:55:00.000

START_OF_MINUTE                                        
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:59:00.000
1760-01-01 00:00:00.000
1799-12-31 23:59:00.000
1800-01-01 00:00:00.000
2009-03-24 17:03:00.000
2014-05-24 16:56:00.000
9999-12-31 23:59:00.000

START_OF_SECOND                                        
------------------------------------------------------ 
1753-01-01 00:00:00.000
1759-12-31 23:59:59.000
1760-01-01 00:00:00.000
1799-12-31 23:59:59.000
1800-01-01 00:00:00.000
2009-03-24 17:03:53.000
2014-05-24 16:56:27.000
9999-12-31 23:59:59.000

CODO ERGO SUM

CLages
Posting Yak Master

116 Posts

Posted - 2006-10-24 : 15:11:05

The main problem is that
Always i convert to SMALLDATETIME, the CONVERT round the Date, I need
to Truncate.

I can not Now change the Column from smalldatetime to datetime

tks
Clages

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-10-24 : 15:28:46

I only miss:

F_DANCERS_AT_THE_END_OF_TIME()

rockmoose

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-01 : 07:54:21

What about dbo.F_START_OF_X_MIN( @DAY, @Interval ) ? Then divide and multiply with @Interval?
I needed this function, today, so I took the liberty to write the function as F_START_OF_6_MIN, because I needed to calculate to a 1/10th of an hour.
Then I realized the function could accept this interval as a parameter.

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-01 : 16:25:21

quote:
Originally posted by Peso

What about dbo.F_START_OF_X_MIN( @DAY, @Interval ) ? Then divide and multiply with @Interval?
I needed this function, today, so I took the liberty to write the function as F_START_OF_6_MIN, because I needed to calculate to a 1/10th of an hour.
Then I realized the function could accept this interval as a parameter.

Peter Larsson
Helsingborg, Sweden

Added F_START_OF_X_MIN to script.

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-24 : 19:03:55

Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code:
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-25 : 16:58:08

quote:
Originally posted by Michael Valentine Jones

Modified the following functions to use a simpler algorithm, that is shorter, runs faster, and is more suited for use with in-line code:
dbo.F_START_OF_30_MIN( @DAY )
dbo.F_START_OF_20_MIN( @DAY )
dbo.F_START_OF_15_MIN( @DAY )
dbo.F_START_OF_10_MIN( @DAY )
dbo.F_START_OF_05_MIN( @DAY )

CODO ERGO SUM

I don't know what the change was, but this seems even simpler?:

declare @Day datetime; set @Day = getdate()


select	@day
	,dateadd(mi,(datepart(mi,@Day)/15)*15,dateadd(hh,datediff(hh,0,@Day),0))	as mvj
	,dateadd(mi,datediff(mi,0,@Day)-datepart(mi,@day)%15,0)				as rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-25 : 18:13:29

quote:
Originally posted by rockmoose
I don't know what the change was, but this seems even simpler?:

declare @Day datetime; set @Day = getdate()

select @day
,dateadd(mi,(datepart(mi,@Day)/15)*15,dateadd(hh,datediff(hh,0,@Day),0)) as mvj
,dateadd(mi,datediff(mi,0,@Day)-datepart(mi,@day)%15,0) as rockmoose

The second method does work most of the time, but it doesn't meet my design objective to "work for any datetime value from 1753/01/01 00:00:00.000 through 9999/12/31 23:59:59.997"


declare @day datetime
set @day = '99991231 23:59:59'

print 'Method 1'
select M1 = dateadd(mi,(datepart(mi,@Day)/15)*15,dateadd(hh,datediff(hh,0,@Day),0))

print 'Method 2'
select M2 = dateadd(mi,datediff(mi,0,@Day)-datepart(mi,@day)%15,0)

Results:


Method 1
M1                                                     
------------------------------------------------------ 
9999-12-31 23:45:00.000

(1 row(s) affected)

Method 2
Server: Msg 535, Level 16, State 1, Line 8
Difference of two datetime columns caused overflow at runtime.

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-26 : 03:24:10

hmm, yes, it only works up to '5983-01-24 02:07:59.997'

rockmoose

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-26 : 09:41:03

quote:
Originally posted by rockmoose

hmm, yes, it only works up to '5983-01-24 02:07:59.997'

rockmoose

The method you posted would work for the vast majority of applications, but I wanted an algorithm that would work with any datetime. My method is a little more complicated, but still possible to use with in-line code instead of in the function, and that is also something I wanted.

The previous method was the one used in F_START_OF_X_MIN. I didn't change that function, because it would change the results it produces when 60%@INTERVAL <> 0.

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-26 : 10:19:21

quote:
Originally posted by rockmoose

I only miss:

F_DANCERS_AT_THE_END_OF_TIME()

rockmoose

Or

F_LENGTH_OF_LAP_DANCE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-26 : 14:12:40

Good design goal, and for a "library" function I would expect no less.

If I need to store year 6000AD, chances are that I also would need 60000AD or 600000AD, so another datatype altogether might make sense

Reminds me of the Y2K bug, I't hate to leave an inbuilt Y5983 bug!

<- Pun

> F_LENGTH_OF_LAP_DANCE

That's great...

Now we need:
F_TIME_OF_MARGERITA()

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-26 : 14:18:14

quote:
Originally posted by rockmoose

Good design goal, and for a "library" function I would expect no less.

If I need to store year 6000AD, chances are that I also would need 60000AD or 600000AD, so another datatype altogether might make sense

Reminds me of the Y2K bug, I't hate to leave an inbuilt Y5983 bug! <- Pun

> F_LENGTH_OF_LAP_DANCE
That's great...

Now we need:
F_TIME_OF_MARGERITA()

Nobody seems too worried about the SQL Server Y10K bug.

I'm more interested in the F_START_OF_LAP_DANCE.

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-02-26 : 14:40:18

> Nobody seems too worried about the SQL Server Y10K bug.
M$ has an army of consultants ready to tackle that one, and they are not the lea$t worried.

F_START_OF_LAP_DANCE, hmm yeah, I'd like to see an optimal implementation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources