Tag: SQL Server

Below script will help to drop the scalar function present in SQL Server 2005 if exist.

It is always good practice to write the drop statement before creating any stored procedure or the database function.
Lets say the function name is fn_GetFirstDayOfWeek

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetFirstDayOfWeek]')
AND type in (N'FN', N'IF',N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetFirstDayOfWeek]

in some scenario we comes in very strange requirement. with me it happens. i needed to calculate date in which fort night the date is coming and on the basis of that calculate start and end date. so i have created below script. I hope it will be useful to readers.

DECLARE @InputDate DATETIME
SET @InputDate = '17-Jul-2010'

DECLARE @FortStartDate DATETIME
DECLARE @FortEndDate DATETIME

IF DAY(@InputDate) < 16 --First FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)
SET @FortEndDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/15' AS DATETIME)
END
ELSE --Second FortNight
BEGIN
SET @FortStartDate = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/16' AS DATETIME)
SET @FortEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))
END

SELECT @FortStartDate as 'StartDate'
SELECT @FortEndDate as 'EndDate'

This time lets create the Scalar function in SQL Server to get the First Day and Last Day of the Week.

Create below Scalar function in SQL Server to get the First Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DateTime

	-- Add the T-SQL statements to compute the return value here
	-- 1 -> Sunday, 7 -> Saturday
	SELECT @Result = DATEADD(DAY, 1- DATEPART(DW, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

Create below Scalar function in SQL Server to get the Last Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DateTime

	-- Add the T-SQL statements to compute the return value here
	-- 1 -> Sunday, 7 -> Saturday
	SELECT @Result = DATEADD(DAY, 7- DATEPART(DW, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

To get the values from function, run below script

 select dbo.GetFirstDayWeek(GETDATE()) as StartDate
 select dbo.GetLastDayWeek(GETDATE()) as EndDate

as i said, remember that to call function we have to call like dbo.functionName

One of the features i love about SQL Server is function supports. Divide your task in functions just like other programming language. In this article i am going to show you that how to display the First Day and Last Day of Month in SQL Server.

Create a below function to get the First Day of Month:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)

-- Return the result of the function
RETURN @Result

END
GO

Now, Create Below Function to get the Last day of month.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDay
(
-- Add the parameters for the function here
@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
-- Declare the return variable here
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
SELECT @Result = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@InputDate)+1,0))

-- Return the result of the function
RETURN @Result

END
GO

To get the values from function, run below script

select dbo.GetFirstDay(GETDATE()) as StartDate
select dbo.GetLastDay(GETDATE()) as EndDate

remember that to call function we have to call like dbo.functionName