Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLBIGeek’s Function Friday – Return Last Date of the Prior Month

 

 

Brian K. McDonald

Brian K. McDonald

SQLBIGeek

Twitter: @briankmcdonald

 

Welcome to the fourth post of my “SQLBIGeek’s Function Friday” blog series. In this series, I am hoping that I can provide some of the details about functions that I have either found on the internet or something that I had to create to meet a need. If it is something that I come across, I will attempt to make it shorter and/or perform better. If it is something I wrote, it is just something to share with you J Either way, I hope that you enjoy the series and that you can get some useful ideas or functions to use in your environment.

 

 

I can’t remember what I used this for when I created it, but I thought that I would share it none-the-less. Somewhat related to my prior blog on getting the first day of month, this can be used to get the last day of the prior month based on whatever date that is passed into the function.

 

Script 1: Function

CREATE FUNCTION [dbo].[ufn_LastDayOfPriorMonth]

(

      @Date SMALLDATETIME

/*=========================================================================

Created By: Brian K. McDonald, MCDBA, MCSD (www.SQLBIGeek.com)

Email:      bmcdonald@SQLBIGeek.com

Twitter:    @briankmcdonald

Date:       10/29/2010

Purpose:    Return the last day of the prior month based on date

            passed into the function

                       

Usage:      SELECT dbo.ufn_LastDayOfPriorMonth ('10/29/2010')

----------------------------------------------------------------------------

Modification History

----------------------------------------------------------------------------

 

==========================================================================*/

)

RETURNS SMALLDATETIME

AS 

BEGIN

 

      DECLARE @LastDayOfPriorMonth SMALLDATETIME

      SET @LastDayOfPriorMonth = DATEADD(d,(-1*DATEPART(d,CONVERT(VARCHAR(12),@Date,101))),CONVERT(VARCHAR(12),@Date,101))

      RETURN @LastDayOfPriorMonth

     

END

GO

 

--A few sample executions

SELECT AdventureWorks.dbo.ufn_LastDayOfPriorMonth('1/10/2010')    --returns 12/31/2009

SELECT AdventureWorks.dbo.ufn_LastDayOfPriorMonth('6/15/2010')    --returns 5/31/2010

SELECT AdventureWorks.dbo.ufn_LastDayOfPriorMonth('10/29/2010')   --returns 9/30/2010

SELECT AdventureWorks.dbo.ufn_LastDayOfPriorMonth('11/29/2010')   --returns 10/31/2010

 

--Clean up

DROP FUNCTION dbo.ufn_LastDayOfPriorMonth

 

Please be sure to return for the next BISQLGeek's Function Friday! I hope that you have enjoyed this post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald

 ** Corrected Content 11/11/2010 **

 

Comments

Posted by Brian K. McDonald on 6 November 2010

In a rush before my battery died last night, I posted the wrong title for this post. I guess one could say that I pasted the wrong content, but either way, the title and the content are mix matched. :) I am sorry for that. I did correct it on my primary blogging site and the "Return Last Date of the Prior Month" blog can be found here: www.bidn.com/.../sqlbigeek’s-function-friday-–-return-last-date-of-the-prior-month

First Day Of Month Function here: www.bidn.com/.../sqlbigeek’s-function-friday-–-return-first-date-of-month

Leave a Comment

Please register or log in to leave a comment.