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.

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.

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.

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.

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 )

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 )

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.