Finding Date Closest to Middle or Last Day of Month in SQL

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’))
DROP FUNCTION [dbo].[fn_ClosestPeriod];

Create FUNCTION fn_ClosestPeriod(@nowdate datetime)
RETURNS 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
then @MiddleofMonth
When @nowDate > @MiddleofMonth and @nowDate< @lastDayofMonth
then @lastDayofMonth

RETURN (@mReturnDate)


To use this function, use it like this

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

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

Tags: , , , , , , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

One Comment on Finding Date Closest to Middle or Last Day of Month in SQL

  1. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *