how to get first date of weeks for specific year.

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

General SQL Server Forums

New to SQL Server Programming

how to get first date of weeks for specific year.

Author

Topic

Ichcha
Starting Member

3 Posts

Posted - 2011-08-04 : 02:16:10

hi experts, please help.

how could i get first date for all 52 weeks for the year 2011?

expected output :
week | first_date
1 | 2011-01-01
2 | 2011-01-08
3 | 2011-01-15

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-04 : 02:48:54

your week starts on Saturday ?

KH
[spoiler]Time is always against us[/spoiler]

Ichcha
Starting Member

3 Posts

Posted - 2011-08-04 : 03:16:29

yes i set datefirst to 6

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-04 : 03:53:39

[code]DECLARE @Year SMALLINT = 2011

;WITH cteSource(theDate)
AS (
SELECT DATEADD(DAY, 7 * v.Number, o.Origin) AS theDate
FROM (
SELECT DATEADD(DAY, DATEDIFF(DAY, '18991230', STR(@Year, 4, 0) + '0101') / 7 * 7, '18991230')
) AS o(origin)
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND 52
)
SELECT ROW_NUMBER() OVER (ORDER BY theDate) AS WeekNum,
theDate
FROM cteSource
WHERE DATEPART(YEAR, theDate) = @Year[/code]

N 56°04'39.26"
E 12°55'05.63"

Ichcha
Starting Member

3 Posts

Posted - 2011-08-04 : 04:16:40

thanks SwePeso,

since am new to sql i couldn't understand your code fully, can you please explain abit and where can i change the code to get last date also?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources