CODEWITHSUNDEEP
sqlsql-serversql-server-2000
Gopal
Gopal

Reputation: 11982

How can i find the particular days

I have the date value like this - 12/2011 or 11/2011 (MM/yyyy)

I want to find the sundays on the particular month....

For Example, If i select the month 01/2012, The query should give the result like this

01
08
15
22
29

The above date are sunday.

Expected Output for 01/2012 Month

01
08
15
22
29

How to make a query

Need Query Help

Upvotes: 1

Views: 187

Answers (4)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

With a little help of a number table (master..spt_values)

declare @M varchar(7)
set @M = '01/2012'

declare @D datetime
set @D = convert(datetime, '01/'+@M, 103)

set datefirst 7

select dateadd(day, N.Number, @D)
from master..spt_values as N
where N.type = 'P' and
      dateadd(day, N.Number, @D) >= @D and
      dateadd(day, N.Number, @D) < dateadd(month, 1, @D) and
      datepart(weekday, dateadd(day, N.Number, @D)) = 1

Upvotes: 4

Fionaa Miller
Fionaa Miller

Reputation: 511

You can change date format and use DateName function.

SELECT DateName(dw, GETDATE())

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21766

Here it comes:

SET DATEFIRST 1
DECLARE @givenMonth CHAR(7)
SET @givenMonth = '12/2011'

DECLARE @month INT 
SET @month = CAST(SUBSTRING(@givenMonth, 1, 2) AS INT)
DECLARE @year INT 
SET @year = CAST(SUBSTRING(@givenMonth, 4, 4) AS INT)

DECLARE @Date DATETIME 
SET @Date = DATEADD(month, @month - 1, CAST(CAST(@year AS CHAR(4)) AS DATETIME))
DECLARE @nextMonth DATETIME 
SET @nextMonth = DATEADD(MONTH, 1, @date)

DECLARE @firstSunday DATETIME 
SET @firstSunday = DATEADD(day, 7 - DATEPART(weekday, @date), @date)
CREATE TABLE #Days(Sunday INT)

WHILE @firstSunday < @nextMonth
BEGIN
    INSERT #Days
    SELECT DATEPART(DAY, @firstSunday) Sunday

    SET @firstSunday = DATEADD(day, 7, @firstSunday) 
END
SELECT Sunday
FROM #Days
ORDER BY 1

DROP TABLE #Days

Upvotes: 4

gbn
gbn

Reputation: 432271

Edit: use a numbers table in place of the CTE because you are in SQL Server 2000. C'mon, upgrade and do yourself a favour

DECLARE @monthyear varchar(10) = '11/2012';
DECLARE @start smalldatetime, @end smalldatetime;

-- use yyyymmdd format
SET @start = CAST(RIGHT(@monthyear, 4)+ LEFT(@monthyear, 2) + '01' AS smalldatetime);
-- work backwards from start of next month
SET @end = DATEADD(day, -1, DATEADD(month, 1, @start));

-- recursive CTE. Would be easier with a numbers table
;WITH daycte AS
(
    SELECT @start AS TheDay
    UNION ALL
    SELECT DATEADD(day, 1, TheDay) 
    FROM daycte
    WHERE TheDay < @end
)
SELECT DATENAME(day, TheDay)
FROM daycte 
-- One of many ways. 
-- This is independent of @@datefirst but fails with Chinese and Japanese language settings
WHERE DATENAME(weekday, TheDay) = 'Sunday';

Upvotes: 1

Related Questions