Moving Week Ending Dates - SQL Server Forums

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

Moving Week Ending Dates

Author

Topic

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2010-12-02 : 22:55:45

Hi I have this formulas:

select
LEFT(DATENAME(month, DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 7 * 7, '19000107') +7), 3) + ' ' + DATENAME(day, DATEADD(DAY,DATEDIFF(DAY, '19000107', GETDATE()) / 7 * 7, '19000107') +7) AS Week4,
LEFT(DATENAME(month, DATEADD(DAY, DATEDIFF(DAY, '19000107',GETDATE()) / 7 * 7, '19000107') - 0), 3) + ' ' + DATENAME(day, DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 7 * 7, '19000107') - 0) AS Week3,
LEFT(DATENAME(month, DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 7 * 7, '19000107') - 7), 3) + ' ' + DATENAME(day, DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 7 * 7, '19000107') - 7) AS Week2,
LEFT(DATENAME(month, DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 7 * 7, '19000107') - 14), 3) + ' ' + DATENAME(day, DATEADD(DAY, DATEDIFF(DAY, '19000107', GETDATE()) / 7 * 7, '19000107') - 14) AS Week1

What I want to have is Week1 always the 7th of the month
Week 2 14th of the month
Week 3 21st of the month
Week 4 is the last day of the month

Is this possible? Something that rolls every week.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-12-02 : 23:37:48

Use the F_TABLE_DATE function here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519


Select DATE, CASE WHEN DAY(DATE)<=7 then 'Week 1'
				  When DAY(DATE)<=14 then 'Week 2'
				  When DAY(Date)<=21 then 'Week 3'
				  else 'Week 4' end as modWEEK

FROM dbo.F_TABLE_DATE ('20100101','20100228')

Or you could just pass whatever date you needed into that case when expression.

the F_TABLE_DATE table function is quite handy for this type of thing..just pass your date ranges in and instant calendar.

Poor planning on your part does not constitute an emergency on my part.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-02 : 23:46:59

[code]
select Week1 = dateadd(month, datediff(month, 0, getdate()), 6),
Week2 = dateadd(month, datediff(month, 0, getdate()), 13),
Week3 = dateadd(month, datediff(month, 0, getdate()), 20),
Week4 = dateadd(month, datediff(month, 0, getdate()) + 1, -1)
[/code]

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

BankOfficerHere
Posting Yak Master

124 Posts

Posted - 2010-12-02 : 23:58:29

Thanks. The results what I want to have is

Week 1 = December 7
Week 4 = November 30
Week 3 = November 21
Week 2 = November 14

Then comes next week:

Week 2 = December 14
Week 1 = December 7
Week 4 = November 30
Week 3 = November 21

It's rolling

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-12-03 : 02:01:37

[code]
declare @dte datetime

select @dte = '2010-12-03'

select Week1 = dateadd(month, datediff(month, 0, @dte), 6),
Week2 = dateadd(month, datediff(month, 0, @dte - 7), 13),
Week3 = dateadd(month, datediff(month, 0, @dte - 14), 20),
Week4 = dateadd(month, datediff(month, 0, @dte - 21) + 1, -1)
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources