Week Part

Question

Week Part

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 1

Michael Valentine Jones (11/12/2010)
Just a little nitpicking on this; Jeff's code does not work on dates before 1899-12-25.

Heh... actually, nitpicking appreciated here. I'm one of the first to always say not to limit the code for any reason and there I went with limited code. Thanks for the followup.

Just a little nitpicking back... your code will fail for dates prior to 1753. (Just teasing, ol' friend.) :-D:-P:-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

WayneS (11/12/2010)
Michael Valentine Jones (11/12/2010)
Note that any Monday date, not just 1753-01-01, can be used as long as it is earlier than OriginalDate. I chose 1753-01-01 because it is the earliest possible datetime that is a Monday, so there is no possibility of OriginalDate being earlier.
:ermm: Guess we need a new routine to handle SQL 2008's new DATE and DATETIME2 formats. '0001-01-01' is now the earliest known Monday.

BWAA-HAAA!!!! "These are not the droids you're looking for." 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 3

Michael Valentine Jones (11/12/2010)
End of Week Function available on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760

Michael... please double check me but I believe a documentation change is necessary for that code. Here's what I get when I apply the numbers 1 - 7 as the @WEEK_START_DAY parameter as week-ending days...

1 = Sat, 2 = Sun, 3 = Mon, 4 = Tue, 5 = Wed, 6 = Thu, 7 = Fri

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Jeff Moden (11/14/2010)
Michael Valentine Jones (11/12/2010)
End of Week Function available on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
Michael... please double check me but I believe a documentation change is necessary for that code. Here's what I get when I apply the numbers 1 - 7 as the @WEEK_START_DAY parameter as week-ending days...
1 = Sat, 2 = Sun, 3 = Mon, 4 = Tue, 5 = Wed, 6 = Thu, 7 = Fri

I think you are mis-understanding. @WEEK_START_DAY is actually the week start date not the week end date, so if you pass 1 (for Sunday, it returns the Saturday following the Sunday that started the week.

The link explains as follows:

"This function is a companion to function F_START_OF_WEEK and has the same input parameters, @DATE and @WEEK_START_DAY. If they are called with the same input parameters, they will return the first and last day of the week."

select

a.DT,

b.StartDW,

StartOfWeek =

dbo.F_START_OF_WEEK(a.DT,b.StartDW) ,

EndOfWeek =

dbo.F_END_OF_WEEK(a.DT,b.StartDW)

from

-- Test Date

( select DT = convert(datetime,'20101116') ) a

cross join

( -- Test Week Start Date

select StartDW = 1 union all

select StartDW = 2 union all

select StartDW = 3 union all

select StartDW = 4 union all

select StartDW = 5 union all

select StartDW = 6 union all

select StartDW = 7

) b

DT StartDW StartOfWeek EndOfWeek

----------------------- ------- ----------------------- -----------------------

2010-11-16 00:00:00.000 1 2010-11-14 00:00:00.000 2010-11-20 00:00:00.000

2010-11-16 00:00:00.000 2 2010-11-15 00:00:00.000 2010-11-21 00:00:00.000

2010-11-16 00:00:00.000 3 2010-11-16 00:00:00.000 2010-11-22 00:00:00.000

2010-11-16 00:00:00.000 4 2010-11-10 00:00:00.000 2010-11-16 00:00:00.000

2010-11-16 00:00:00.000 5 2010-11-11 00:00:00.000 2010-11-17 00:00:00.000

2010-11-16 00:00:00.000 6 2010-11-12 00:00:00.000 2010-11-18 00:00:00.000

2010-11-16 00:00:00.000 7 2010-11-13 00:00:00.000 2010-11-19 00:00:00.000

Function F_START_OF_WEEK available here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 5

Looking at all the weekday examples, here is my 2 cents.

SELECT -- convert StartDate to weekdays

CASE

WHEN DATEPART(day,[StartDate]) - datepart(day,DATEADD(ww,0,getdate())) = 0 THEN 'MON'

WHEN DATEPART(day,[StartDate]) - datepart(day,DATEADD(ww,0,getdate())) = 1 THEN 'TUE'

WHEN DATEPART(day,[StartDate]) - datepart(day,DATEADD(ww,0,getdate())) = 2 THEN 'WED'

WHEN DATEPART(day,[StartDate]) - datepart(day,DATEADD(ww,0,getdate())) = 3 THEN 'THU'

WHEN DATEPART(day,[StartDate]) - datepart(day,DATEADD(ww,0,getdate())) = 4 THEN 'FRI'

WHEN DATEPART(day,[StartDate]) - datepart(day,DATEADD(ww,0,getdate())) = 5 THEN 'SAT'

WHEN DATEPART(day,[StartDate]) - datepart(day,DATEADD(ww,0,getdate())) = 6 THEN 'SUN'

END AS [Day]

WHERE -- force query selection to this current week

AND DATEPART(day,[StartDate]) >= datepart(day,DATEADD(ww,0,getdate())) -- beginning of week

AND DATEPART(day,[StartDate]) < datepart(day,DATEADD(ww,1,getdate())) -- end of week

Note:

for the above example, [StartDate] = '2010-11-15' to '2010-11-21'

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 6

Michael Valentine Jones (11/15/2010)
Jeff Moden (11/14/2010)
Michael Valentine Jones (11/12/2010)
End of Week Function available on this link:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
Michael... please double check me but I believe a documentation change is necessary for that code. Here's what I get when I apply the numbers 1 - 7 as the @WEEK_START_DAY parameter as week-ending days...
1 = Sat, 2 = Sun, 3 = Mon, 4 = Tue, 5 = Wed, 6 = Thu, 7 = Fri
I think you are mis-understanding. @WEEK_START_DAY is actually the week start date not the week end date, so if you pass 1 (for Sunday, it returns the Saturday following the Sunday that started the week.

I'm going to blame my mistake on being overworked and not having enough coffee. Thanks, Michael.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)