August 2, 2010 at 10:43 am
Hey Guys,
SQL newb here. Can someone please step me through exactly what this variable assignment is
doing? Specifically "ELSE DATEADD(DD,-DATEPART(DW,@STARTDATE)+1"
SELECT @SUNDAY = CONVERT(VARCHAR(10)
,(SELECT CASE WHEN DATENAME(DW,@STARTDATE)
= 'SUNDAY' THEN @STARTDATE
ELSE DATEADD(DD,-DATEPART(DW,@STARTDATE)+1,@STARTDATE)
END)
,101)
Thanks!!
August 2, 2010 at 10:46 am
I should add that @STARTDATE is assigned to GETDATE
August 2, 2010 at 11:13 am
Ricardumus (8/2/2010)
Hey Guys,SQL newb here. Can someone please step me through exactly what this variable assignment is
doing? Specifically "ELSE DATEADD(DD,-DATEPART(DW,@STARTDATE)+1"
SELECT @SUNDAY = CONVERT(VARCHAR(10)
,(SELECT CASE WHEN DATENAME(DW,@STARTDATE)
= 'SUNDAY' THEN @STARTDATE
ELSE DATEADD(DD,-DATEPART(DW,@STARTDATE)+1,@STARTDATE)
END)
,101)
Thanks!!
DATEPART(DW, @startdate) = numberic day of week of @startdate (by default, Sunday is 1)
-DATEPART(DW, @startdate)+1 = negate and add 1. If Sunday, this section returns 0. Monday, -1. etc...
DATEADD(DD, <above>, @startdate) = add above days to @startdate. We are adding 0 to Sunday, -1 to Monday, -2 to Tuesday. In other words, this will always return a Sunday.
Your code returns the first day of the week. It's a fairly common function. You don't need the CASE statement.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply