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.
Parameter In | Value Out |
03/16/2014 | 03/31/2014 |
03/10/2014 | 03/15/2014 |
03/01/2014 | 03/15/2014 |
03/27/2014 | 03/31/2014 |
..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!
Hi there! Do you use Twitter? I’d like to follow you if that would
be ok. I’m absolutely enjoying your blog and look forward to new posts.