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|
..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 (herehere or here) I created this function to allow this exact return. I realize that it can be altered and maybe optimized but it works.
–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’))
DROP FUNCTION [dbo].[fn_ClosestPeriod];
Create FUNCTION fn_ClosestPeriod(@nowdate datetime)
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
When @nowDate > @MiddleofMonth and @nowDate< @lastDayofMonth
To use this function, use it like this
SELECT [dbo].[fn_ClosestPeriod] (’03/16/2014′) AS [Ending Period]
Looking for quality web hosting? Look no further than Arvixe Web Hosting!