Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted May 27, 2003

GetDate Function and Stored Procedure

By DatabaseJournal.com Staff



>>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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM