http://www.sqlservercentral.com/blogs/briankmcdonald/2010/11/06/sqlbigeek_1920_s-function-friday-_1320_-return-first-date-of-month/

Printed 2014/04/16 04:48PM

SQLBIGeek’s Function Friday – Return First Date of Month

By Brian K. McDonald, 2010/11/06

Brian K. McDonald

Brian K. McDonald

SQLBIGeek

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

GO

 

CREATE FUNCTION [dbo].[ufn_FirstDayOfMonth]

(

      @Date SMALLDATETIME

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

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

Email:      bmcdonald@SQLBIGeek.com

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

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

 

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

)

RETURNS SMALLDATETIME

AS 

BEGIN

 

DECLARE @FirstDayOfMonth SMALLDATETIME

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

      RETURN @FirstDayOfMonth

     

END

GO

 

--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: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN

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

 

 

 

 

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.