If you have ever worked in SQL or other databases you know that dates can be very tricky. It is not always simple to capture differences and length of time when it comes to dates. With this in mind I had a very specific use for a function that will give me the date closest to but not after the middle of the month or the last day of the month. For a visual representation see the chart below.

..and so on.

This comes in handy when you need things to run on the last day of the month or the middle of the month. Taking several pieces of code from multiple sources (here and here or here) I created this function to allow this exact return. I realize that it can be altered and maybe optimized but it works.

[code language=”sql”]
–Creates the Function to randomly generate string
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[fn_ClosestPeriod]’) AND type IN (N’FN’, N’FS’, N’FT’, N’TF’, N’IF’))
BEGIN
DROP FUNCTION [dbo].[fn_ClosestPeriod];
END
GO

Create FUNCTION fn_ClosestPeriod(@nowdate datetime)
RETURNS datetime
AS
BEGIN

Declare @nowMonth int
Declare @nowDay int
Declare @nowYear int
Declare @lastDayofMonth datetime
Declare @MiddleofMonth datetime
Declare @FirstofMonth datetime
Declare @mReturnDate datetime

Set @nowMonth = datepart(month,@nowdate)
Set @nowDay = datepart(day,@nowdate)
Set @nowYear = datepart(year,@nowdate)
Set @FirstofMonth=cast(cast(@nowMonth as varchar(2)) + ‘/01/’ + cast(@nowYear as varchar(4)) as datetime)
Set @LastDayofMonth= DateAdd(s,-86399,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@nowdate)+1,0)))
Set @MiddleofMonth = cast(cast(@nowMonth as varchar(2)) + ‘/15/’ + cast(@nowYear as varchar(4)) as datetime)

Set @mReturnDate = (SELECT case
When @nowdate >= @FirstofMonth and @nowDate <=@MiddleofMonth
then @MiddleofMonth
When @nowDate > @MiddleofMonth and @nowDate< @lastDayofMonth
then @lastDayofMonth
End)

RETURN (@mReturnDate)
End

Go
[/code]

To use this function, use it like this

[code language=”sql”]
SELECT [dbo].[fn_ClosestPeriod] (’03/16/2014′) AS [Ending Period]
[/code]

Looking for quality web hosting? Look no further than Arvixe Web Hosting!