On Thursday, September 17, 2009 at 1:15:01 PM UTC+5:30, Nikhil wrote:
I enter a date in one column, in the second column i want the day for that
date. If the day happens to be the 2nd or the 4th saturday in that month, it
should get highlighted. How do i calculate the 2nd & 4th saturdays?

Rgds

DECLARE @firstday AS DATETIME
DECLARE @lastDay AS DATETIME

--SET @today = @dutydate
SET @today = GETDATE()


SET @firstday = DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0)
SET @lastday = DATEADD(DAY, -1, DATEADD(MONTH, 1, @firstday))

--SELECT @today AS [Today], @firstday AS FirstDayOfMonth, @lastday AS LastDayOfMonth

;WITH MyDates AS
(
SELECT @firstday AS MyDate, DATENAME(DW, @firstday) AS NameOfDay
UNION ALL
SELECT DATEADD(DAY, 1, MyDate) AS MyDate, DATENAME(DW, DATEADD(DAY, 1, MyDate)) AS NameOfDay
FROM MyDates
WHERE DATEADD(DAY, 1, MyDate)<@lastDay
)
SELECT MyDate
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY NameOfDay ORDER BY MyDate) AS RowNo, *
FROM MyDates
) AS T
WHERE (RowNo=2 OR RowNo=4) AND NameOfDay = 'Saturday'