Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Date for 2nd Tues of Month

Author  Topic 

kaus
Posting Yak Master

179 Posts

Posted - 2003-08-04 : 17:33:58
Is there a function where I could get the date (eg ..8/12/2003) If I specify the 1) month and 2) 2nd or 3rd Tues of that month for a given year ??

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-04 : 17:36:41
Here's a function that I wrote (found most of the code on the internet though) that returns the first Sunday of a given date:



CREATE FUNCTION udf_FirstSundayOfTheMonth
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
SELECT @number = 1
SELECT @weekday = 0
WHILE @weekday <> 1
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number + 1
END
RETURN @day
END





Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-04 : 17:50:52
The code below gives you the 2nd Tuesday of a given date:


DECLARE @date datetime
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
DECLARE @WhichOne INT

SELECT @WhichOne = 2
SELECT @number = 1
SELECT @weekday = 0
SELECT @date = getdate()

WHILE @weekday <> 3
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number + 1
END

SELECT @day = DATEADD(d, (@WhichOne -1)* 7, @day)

PRINT @day



Just change the 3 in the WHILE statement to get the answer for a different day of the week. Just change @WhichOne to get the first, third, fourth, or fifth of the month. Probably should add a check to see if the date is still in the current month which would happen if you set @WhichOne to 6.

Tara
Go to Top of Page

kaus
Posting Yak Master

179 Posts

Posted - 2003-08-04 : 18:04:05
Thanks very much -- I think I understand how it is working -- the first example anyway. I havent worked with user defined functions before -- I'm running it in Query Analyzer but dont see a result -- I must be missing something

Pete
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-04 : 18:10:40
The first example just creates the UDF. It does not run it. To run it:

SELECT dbo.udf_FirstSundayOfTheMonth(getdate())

OR

SELECT dbo.udf_FirstSundayOfTheMonth('Jan 15 2002')

The second example is not in UDF form, but it gives the second Tuesday of the month. Read the information in the post to change the values to get other Tuesdays in the month or even other days of the month. Here it is as a UDF:


CREATE FUNCTION udf_SecondTuesdayOfTheMonth
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
DECLARE @WhichOne INT

SELECT @WhichOne = 2
SELECT @number = 1
SELECT @weekday = 0

WHILE @weekday <> 3
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number + 1
END

SELECT @day = DATEADD(d, (@WhichOne -1)* 7, @day)

RETURN @day
END



To run it:

SELECT dbo.udf_SecondTuesdayOfTheMonth('August 15 2003')



Tara
Go to Top of Page

kaus
Posting Yak Master

179 Posts

Posted - 2003-08-04 : 18:17:53
Thanks - I was trying EXEC in Query Analyzer
Go to Top of Page
   

- Advertisement -