Date for 2nd Tues of Month - SQL Server Forums

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

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

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

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

kaus
Posting Yak Master

179 Posts

Posted - 2003-08-04 : 18:17:53

Thanks - I was trying EXEC in Query Analyzer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources