Get every Sunday's date?

Hi,

Is any way to get every Sunday's date?
Any function, reference or something else?
Thanks for help!

Angi

Assuming your SQL Server is set to have Sunday as the first day of the week,
the following will do the trick for all Sundays in 2006:

declare @date datetime

set @date = '1 Jan 2006'

while datepart ( dd, @date ) <> 1

-- Find the next Sunday, OK 1 Jan 2006 was one, so this does nothing

set @date = dateadd ( dd, 1, @date )



-- Now loop till we get to 1 Jan 2007

while @date < '1 Jan 2007'

begin

print @date

set @date = dateadd ( dd, 7, @date )

end



"angi" <angi@news.microsoft.com> wrote in message
news:et04YhjWGHA.5096@TK2MSFTNGP03.phx.gbl...
> Hi,
>
> Is any way to get every Sunday's date?
> Any function, reference or something else?
> Thanks for help!
>
> Angi
>

angi  wrote:
> Hi,
>
> Is any way to get every Sunday's date?
> Any function, reference or something else?
> Thanks for help!
>
> Angi

Use a calendar table:
http://www.aspfaq.com/show.asp?id=2519

SELECT dt
FROM calendar
WHERE dayname = 'Sunday'
AND dt >= '20000101'
AND dt < '21990101' ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Another way is to use a number table if you don't want to keep a
calendar table

--Create the number table once!
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=10000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END
GO


SET DATEFIRST 7 --Just in case!
-- Get Sunday's from 2000-01-01
DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2000-01-01 00:00:00.000'

SELECT
 DATEADD(dd,numberID,
 @dtmDate),DATEPART(d
 w,DATEADD(dd,numberI
D,@dtmDate)),DAT
 ENAME(dw,DATEADD(dd,
numberID,@dtmDate))
FROM dbo.NumberPivot
WHERE NumberID < 10000
AND  DATEPART(dw,DATEADD(
 dd,numberID,@dtmDate
)) =1

or just this, if you know that you start from a Sunday

DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2000-01-02 00:00:00.000'

SELECT  DATEADD(wk,numberID,
@dtmDate)
FROM dbo.NumberPivot
WHERE NumberID < 100


Denis the SQL Menace
http://sqlservercode.blogspot.com/

Great link, thanks David. Especially the part about Week of Year. Am facing
the same stupid problem again and again.


"David Portas" < REMOVE_BEFORE_REPLYI
NG_dportas@acm.org> wrote in message
news:1144417384.576842.78840@u72g2000cwu.googlegroups.com...
> angi wrote: 
>
> Use a calendar table:
> http://www.aspfaq.com/show.asp?id=2519
>
> SELECT dt
> FROM calendar
> WHERE dayname = 'Sunday'
>  AND dt >= '20000101'
>   AND dt < '21990101' ;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>