GetDate Function and Stored Procedure

>>Script Language and Platform: MS SQL 2000 ,MS SQL 7.0
Get a specific weekday of a month by passing any date.

–Usage explanation for Function
Getting Second Tuesday of May/2003 by executing
select dbo.UDF_getdate(2,3,’5/26/2003′)

–Usage explanation for Stored Procedure
Getting the fifth Friday of May/2003 by excuting
exec usp_getdate 5,6,’5/1/2003′

–Both Function and Stored procedure can detect error inserts.

Author: Claire Hsu

/*

   *	Script Language and Platform:MS SQL 2000

   *	Objecttive: Get specific weekday of a month by passing any date

   *	Usage:Getting Second Tuesday of May/2003 by passing

   *	select dbo.UDF_getdate(2,3,’5/26/2003′)

   *	Author:Claire Hsu

   *	Date  :2003/5/26

   *	Email:messageclaire@yahoo.com

   *	Description:This script contains both funciton and procedure.

   *	You could apply accordingly

*/

–Here is the code for Function

——————————————————————————-

create function dbo.UDF_getdate (@week int,@dw int,@dayw1 varchar(25))

returns datetime

as

begin
declare @v1 int

declare @v2 int

declare @dayw datetime
if isdate(@dayw1) <> 1

begin

set @dayw = null

end
else

begin

set @dayw = convert(datetime,@dayw1 )

	if @dw >8 or @dw <=0 or @week >=6 or @week <=0
	begin
	set @dayw = null
	end

	else
	begin
	set @v1 = month(@dayw)
	set @dayw = cast(convert(char,@v1)+'/1/'+convert(char,year(@dayw)) as datetime)
	set @v2 = datepart(dw,@dayw)
	

		if @v2 >@dw

		begin

		set @dayw = dateadd(d,@week*7-(@v2-@dw),@dayw)

		end
		if @v2<=@dw
		begin
		set @dayw = dateadd(d,(@week-1)*7+(@dw-@v2),@dayw)
		end
	
		if month(@dayw)>@v1

		begin

		set @dayw = null

		end
		else

		begin

		set @dayw = @dayw

		end

	end

end

return(@dayw)

end
–Usage on single select statement

–Example1

–The following statement gives you the Second Sunday of May/2003

–select dbo.UDF_getdate(2,1,’5/35/2003′)
–Example2

–The following statement gives you the Third Wednesday of Feb/2003

–select dbo.UDF_getdate(3,4,’2/14/2003′)
–Example3

–The following statement gives you the Forth Monday of Dec/2003

–select dbo.UDF_getdate(4,2,’12/25/2003′)
–Usage on table

–The following statement gives you the reuslt for the whole table

–select dbo.UDF_getdate(week,weekday,datetime_filed_column) from table_name

—————————————————————————-

—————————————————————————-
–Here is the code for Stored Procedure

create proc USP_getdate @week int,@dw int,@dayw1 varchar(25)

as

declare @v1 int

declare @v2 int

declare @dayw datetime
if isdate(@dayw1) <> 1

begin

raiserror(“Out-of-Range datetime value”,16,1)

end

else
begin

set @dayw = convert(datetime,@dayw1 )

if @dw >8 or @dw <=0 or @week >=6 or @week <=0
	begin
	raiserror("Your insert in invalid wither in week or day of the week",16,1)
	end

	else
	begin
	set @v1 = month(@dayw)
	set @dayw = cast(convert(char,@v1)+'/1/'+convert(char,year(@dayw)) as datetime)
	set @v2 = datepart(dw,@dayw)
	

		if @v2 >@dw

		set @dayw = dateadd(d,@week*7-(@v2-@dw),@dayw)
		if @v2<=@dw
		set @dayw = dateadd(d,(@week-1)*7+(@dw-@v2),@dayw)

		if month(@dayw)>@v1

		raiserror(“Your insert is invalid in week ,the week should be

			less or equal to 4″,16,1)
		else

		select @dayw

		end

	end
–Usage

–exec usp_getdate 2,5,’6/23/2003′ The second Thursday of Jun/2003

–exec usp_getdate 5,6,’5/1/2003′ The fifth Friday of May/2003

Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
“as -is”, without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose…

Disclaimer Continued

Back to Database Journal Home