Apr 15, 2006
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 )
/*
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
*/
CODO ERGO SUM
View 13 Replies
View Related
Jul 19, 2007
I have select statement that returns data on a construction project.
I have a start date, end date, and forecasted cost for each task in the project. I need to create a table that spreads the dollars in a linear fashion broken down by fiscal period.
I have:
task_id, start_date, end_date, cost
1, 9/15/2008, 12/15/2008, 3000
2, 7/1/2008, 12/15/2008, 550
I need
task_id, fiscal_period, cost
1, 200803, 500
1, 200804, 1000
1, 200805, 1000
1, 200806, 500
2, 200801, 100
2, 200802, 100
2, 200803, 100
2, 200804, 100
2, 200805, 100
2, 200806, 50
I can do the math to properly calculate the dollar amount, I am having trouble creating the statement that will process through each row of my select statement and insert multiple rows into the new table.
View 4 Replies
View Related
