MS SQL Server - Get every Sunday's date?

Author

Get every Sunday's date?

angi

2006-04-07, 7:24 am

Hi,

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

Angi

Jan Van der Eecken

2006-04-07, 7:24 am

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
>

David Portas

2006-04-07, 9:23 am

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
--

SQL

2006-04-07, 9:23 am

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)),DATENAM
E(dw,DATEADD(dd,numb
erID,@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/

Jan Van der Eecken

2006-04-07, 11:23 am

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
> --
>

Sponsored Links