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
