IsZero

When you divide the numeric by zero,you'll get the error message.

Message errorServer: Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

You can use this function to solve this problem.

Create functon :

CREATE FUNCTION dbo.iszero(@Value numeric,@Replace int)
RETURNS int
AS
BEGIN
DECLARE @Result int
IF @Value = 0
SET @Result = @Replace
ELSE
SET @Result = @Value
RETURN @Result
END

Test : shows null if function returns null value.

SELECT
Id AS ID,
[IND Volume],
[Volume Grade],
[Volume Grade] / dbo.iszero([IND Volume],null) AS DIV_Result
FROM dbo.LampCustomerAll
WHERE (ASM_Code = 'LM5000') AND ([IND Volume] = 0) OR ([IND Volume] = 2)
ORDER BY Id DESC

Result :

How to create function

How to create function

CREATE FUNCTION owner_name.function_name ( @parameter_name AS data_type ,...n )
RETURNS data_type
AS
BEGIN
function_body
RETURN value
END


Examples :

CREATE FUNCTION dbo.GetNoWeek (@var Varchar(10))
RETURNS varchar(10)
BEGIN
DECLARE @result varchar(10)
IF @var = 'Ok'
SET @Result = 'Ok'
ELSE
SET @Result = 'Not Ok'
RETURN @Result
END

AddZeroDigit

How to add Zero to an integer value from left (like 9 to 009)

Create function :

CREATE FUNCTION dbo.AddZeroDigit(@Value AS int)
RETURNS varchar(3)

BEGIN

DECLARE @Result varchar(3)

SET @Result = RIGHT('000'+CAST(@Value AS varchar(3)),3)

RETURN @ResultEND

Test :

SELECT dbo.AddZeroDigit(9) AS Result

Result :

DayNameOfWeek

How to get the day name of week .

Create function :

CREATE FUNCTION dbo.DayOfWeek(@Date datetime)
RETURNS varchar(10)
BEGINDECLARE @Today intDECLARE @Result varchar(10)
SET @Today = DATEPART(dw, @Date)

IF @Today = 1 SET @Result = 'SUN'
ELSE IF @Today = 2 SET @Result = 'MON'
ELSE IF @Today = 3 SET @Result = 'TUE'
ELSE IF @Today = 4 SET @Result = 'WED'
ELSE IF @Today = 5 SET @Result = 'THU'
ELSE IF @Today = 6 SET @Result = 'FRI'
ELSE IF @Today = 7 SET @Result = 'SAT'
RETURN @ResultEND

Test :

SET DATEFIRST 7
select dbo.DayOfWeek('2007-09-05') as ToDay

Result :

DateNextWeek

How to find the date of Monday or Friday in next week .

Create Function :

CREATE FUNCTION dbo.NextWeek (@toDay datetime,@typeOutput int)
RETURNS datetime
BEGIN DECLARE @noDay int
DECLARE @answer datetime
DECLARE @startDayNextWeek datetime
DECLARE @endDayNextWeek datetime
SET @noDay=DATEPART(dw, @toDay)-1
SET @startDayNextWeek =DATEADD(day,8-@noDay,@toDay)
SET @endDayNextWeek =DATEADD(day,12-@noDay,@toDay)
IF(@typeOutput=1)
SET @answer=@startDayNextWeek --return first day in next week
ELSE SET @answer=@endDayNextWeek --return last day in next week
RETURN (@answer)END

Test :

select dbo.NextWeek ('2007-01-01',2)

Result :

NumberOfWeek

How to find number of week in any year when we know the date.

Create Function :

CREATE FUNCTION dbo.GetNoWeek (@startDate datetime)
RETURNS varchar(10)
BEGIN
DECLARE @answer int
DECLARE @theMonth int
DECLARE @theYear int
DECLARE @firstDate as varchar(10)
SET @theMonth = Month(@StartDate)
SET @theYear = Year(@StartDate)
SET @firstDate = CAST( @theYear as varchar(4))+'-'+CAST(@theMonth as varchar(2))+'-'+'01'
SET @answer =dbo.NoOfDayWeek (CAST(@firstDate as datetime),@startDate,'w')
RETURN (@answer)
END

Test :

select dbo.GetNoWeek('2007-01-31')


Result :

FindDate

What is the date? When you set start date,a number of day and a number week.

Create Function :

CREATE FUNCTION dbo.FindDate (@startDate datetime,@day int,@week int)

RETURNS datetime

BEGIN

DECLARE @answer datetime

DECLARE @dateResult datetime

DECLARE @noDayDateStart int

SET @noDayDateStart=DATEPART(dw, @startDate)-1

SET @dateResult =DATEADD(day,( 7*@week)-(7-@day)-@noDayDateStart,@startDate">7 * @week)-(7 - @day) - @noDayDateStart, @startDate)

SET @answer=@dateResult RETURN (@answer)

END

Test :

select dbo.FindDate ('2007-01-01',1,6)

Result :