Brian K. McDonald
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]
Created By: Brian K. McDonald, MCDBA, MCSD (www.SQLBIGeek.com)
Purpose: Return the last day of the prior month based on date
passed into the function
Usage: SELECT dbo.ufn_LastDayOfPriorMonth ('10/29/2010')
DECLARE @LastDayOfPriorMonth SMALLDATETIME
SET @LastDayOfPriorMonth = DATEADD(d,(-1*DATEPART(d,CONVERT(VARCHAR(12),@Date,101))),CONVERT(VARCHAR(12),@Date,101))
--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
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, MCSDBusiness Intelligence Consultant – Pragmatic Works
Email: firstname.lastname@example.org | Blogs: SQLBIGeek | SQLServerCentral | BIDN
Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald
I apologize, but yesterday, I had the wrong title for the content posted. I removed my blog and reposted with the correct content and name.