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