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

SQLBIGeek’s Function Friday – Return First Date of Month

Brian K. McDonald

Brian K. McDonald


Twitter: @briankmcdonald


Welcome to the third 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.



Here is a quick way to determine the first date of a month based on a parameter passed into the function. Not too difficult, but in my opinion harder to read than my alternate set statement in script 2.


Script 1: Function Using DATEADD and DATEPART

USE AdventureWorks



CREATE FUNCTION [dbo].[ufn_FirstDayOfMonth]




Created By: Brian K. McDonald, MCDBA, MCSD (


Twitter:    @briankmcdonald

Date:       11/6/2010

Purpose:    Return the first day of the month based on date

            passed into the function


Usage:      SELECT dbo.ufn_FirstDayOfMonth ('11/6/2010')


Modification History










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

      RETURN @FirstDayOfMonth





--A few sample executions

SELECT AdventureWorks.dbo.ufn_FirstDayOfMonth('1/10/2010')

SELECT AdventureWorks.dbo.ufn_FirstDayOfMonth('6/15/2010')

SELECT AdventureWorks.dbo.ufn_FirstDayOfMonth('10/29/2010')

SELECT AdventureWorks.dbo.ufn_FirstDayOfMonth('11/29/2010')


DROP PROCEDURE dbo.ufn_FirstDayOfMonth


Alternatively, you could switch out the main set statement with the below to make it a bit easier on the eyes. And brain for many! J

Script 2: Alternate Set Statement

SET @FirstDayOfMonth = CONVERT(SMALLDATETIME, CONVERT(CHAR(4),YEAR(@Date)) + '-' + CONVERT(CHAR(2),MONTH(@Date)) + '-01')


Either way you choose, just know that the query plan created for both of these are identical, so it is a matter of preference. J 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: | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn:







No comments.

Leave a Comment

Please register or log in to leave a comment.