T-SQL DATETIME : Last Day of the Month

Getting the last day of the month, for the given date, by TSQL is bit tricky. Following user defined function provides you a handy general purpose function to get the last day of the month.
It gets the last day of the month as follows:
For example we have a given date 17-March-2007

1. Add a month in the given date by using DateAdd function. It will become 17-April-2007
2. Get the Day part of the date from the above step. It is 17
3. Subtract number of days of the second step from the date of step 1. It will become 31-March-2007
4. Resulting date in step 3 is our answer.

Following is the TSQL code of the function

Create function
udf_GetLastDayOfTheMonth(@m_GivenDate datetime)
returns datetime
as
Begin
Return dateadd(day,-1* day(dateadd(month,1,@m_GivenDate)),dateadd(month,1,@m_GivenDate))

End

Retrieving Employee Hierarchy through CTE

Retrieving subordinates of a given employee or listing sub-nodes of the given node is a classic TSQL problem. This problem can be solved either iteratively by using cursor or recursively by using UDF. Another recursive approach which is now available in SQL Server 2005 instead of UDF is CTE or Common Table Expressions.

Common Table Expression is a much cleaner approach and is highly recommended in these scenarios. Following is a TSQL stored procedure for AdventureWorks database which have typical
EmployeeId and ManagerId columns table to store hierarchy of employees. Following is the code:

Create Proc usp_GetSubordinates(@p_ManagerId int)
as
Begin

With SubCTE
As
(
Select EmployeeId,P.FirstName+‘ ‘ + P.LastName as FullName, 0 as level
From HumanResources.Employee E Inner Join Person.Contact P
ON E.ContactId=P.ContactId
Where EmployeeId=@p_ManagerId

Union All

Select C.EmployeeId,PP.FirstName+‘ ‘ + PP.LastName as FullName, P.level+1
From SubCTE as P
Inner Join HumanResources.Employee C
ON C.ManagerId=P.EmployeeId
Inner Join Person.Contact PP
ON C.ContactId=PP.ContactId

)

Select * from SubCTE;

End

Calculating last day of the month by TSQL

There are several scenarios when you need to calculate the last date of a given month. Problem for the last date is that not every month has the same end date and you can have any thing like 28,29,30,31 depending upon leap year and the required month of the year.
To solve this in a simple query following is a single line code. You can convert it to a UDF also. This query returns the last date of the month for the given date.

—Disable intermediate messages of SQL Server
Set nocount on

—Set date format to day/month/year
Set dateformat ‘dmy’

Declare @m_StartDate datetime

–Set start date as first day of the month
Select @m_StartDate=‘1/12/2007′

—Get the last date of the month
SELECT DateAdd(day, -1, DateAdd( month, DateDiff(month , 0,@m_StartDate)+1 , 0))

Improving Performance of UDF by using SCHEMABINDING

Ever wondered what happens to your queries when you use some UDF which does not touch any table but even then your query becomes slow???
How can we improve that query?
Most of us think that there is no other way except to remove UDF in this scenario.

May be that can be one solution but take a closer look at query plan and you will find “Spooler” operator whose functionality according to Microsoft definition is
“Stores the data from the input into a temporary table in order to optimize rewinds”

SQL Server 2005 uses this operator when the UDF is not SCHEMABIND to ensure that any DDL change will not break the operation of UDF.

This extra step slows down the query execution. To get the best performance, make sure you always bind your UDF even if it is not using any table or view.

Following is the simple example to demonstrate this behavior:

Set NoCount On
–Create Sample Table
CREATE TABLE dbo.tblItems
(
ItemID int NOT NULL IDENTITY (1, 1),
ItemName nvarchar(50) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.tblItems ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
( ItemID )
GO
–Insert 100000 rows
Print ‘Inserting 100000 rows-Start:’+convert(varchar,GetDate(),113)
Go
Declare @m_Counter int
Select @m_Counter=1
While @m_Counter <100001
Begin
Insert Into dbo.tblItems (ItemName) Values
(‘ItemNo:’+convert(varchar,@m_Counter))
Select @m_Counter=@m_Counter+1
End
Go
Print ‘Inserting 100000 rows-Finish:’+convert(varchar,GetDate(),113)
Go
–Create a Sample UDF without Schema Binding
CREATE FUNCTION dbo.SetValueWithoutSchemaBinding(@p_value nvarchar(50))
RETURNS nvarchar(50)
BEGIN
RETURN @p_value+‘-’+ ‘WO’
END
Go
–Create a Sample UDF with Schema Binding
CREATE FUNCTION dbo.SetValueWithSchemaBinding(@p_value nvarchar(50))
RETURNS nvarchar(50)
With SchemaBinding
BEGIN
RETURN @p_value+‘-’+ ‘W’
END
Go
Print ‘Without SchemaBinding Update Start:’+convert(varchar,GetDate(),113)
Go
Update dbo.tblItems
Set ItemName=dbo.SetValueWithoutSchemaBinding(ItemName)
Go
Print ‘Without SchemaBinding Update Finish:’+convert(varchar,GetDate(),113)
Go
Print ‘With SchemaBinding Update Start:’+convert(varchar,GetDate(),113)
Go
Update dbo.tblItems
Set ItemName=dbo.SetValueWithSchemaBinding(ItemName)
Go
Print ‘With SchemaBinding Update Finish:’+convert(varchar,GetDate(),113)
Go

Following is the output which shows 50% improvements (4 seconds to 2 seconds)

Inserting 100000 rows-Start:03 May 2007 00:13:41:670

Inserting 100000 rows-Finish:03 May 2007 00:14:26:590

Without SchemaBinding Update Start:03 May 2007 00:14:26:640

Without SchemaBinding Update Finish:03 May 2007 00:14:30:843

With SchemaBinding Update Start:03 May 2007 00:14:30:843

With SchemaBinding Update Finish:03 May 2007 00:14:32:340