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 (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.
--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
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!