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 :

RoundDown

How to round down any number.

Test :

SELECT ROUND(150.99, 0, 1)

Result :

RoundUP

How to round up any number.


Create function :

CREATE FUNCTION dbo.RoundUP(@Value float )RETURNS int AS
BEGIN
DECLARE @Result int
SELECT @Result = ROUND(@Value,0)
IF @Result < @Value SET @Result = @Result + 1 RETURN @Result END Test :

select dbo.RoundUp(124.000001) as result

Result :