posted 11/6/2010 by briankmcdonald - Views: [2149]
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 DATEPARTUSE AdventureWorksGO CREATE FUNCTION [dbo].[ufn_FirstDayOfMonth] ( @Date SMALLDATETIME/*=========================================================================Created By: Brian K. McDonald, MCDBA, MCSD (www.SQLBIGeek.com)Email: bmcdonald@SQLBIGeek.comTwitter: @briankmcdonaldDate: 11/6/2010Purpose: 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 SMALLDATETIMEAS BEGIN DECLARE @FirstDayOfMonth SMALLDATETIME SET @FirstDayOfMonth = DATEADD(d,(-1*DATEPART(d,CONVERT(VARCHAR(12),@Date,101)))+1,CONVERT(VARCHAR(12),@Date,101)) RETURN @FirstDayOfMonth ENDGO --A few sample executionsSELECT 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 FUNCTION 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! JScript 2: Alternate Set StatementSET @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, MCSDBusiness Intelligence Consultant – Pragmatic WorksEmail: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDNTwitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald
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
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
--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 FUNCTION 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, MCSDBusiness Intelligence Consultant – Pragmatic Works
Email: bmcdonald@pragmaticworks.com | 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.