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:[email protected]
* 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-(@[email protected]),@dayw)
end

if @v2<[email protected] begin set @dayw = dateadd(d,(@week-1)*7+(@[email protected]),@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-(@[email protected]),@dayw)

if @v2<[email protected] set @dayw = dateadd(d,(@week-1)*7+(@[email protected]),@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

Latest Articles