Presents your 
SQL SERVER E-NEWSLETTER for October 17, 2002

<------------------------------------------->

Verify the date for a specific day of a particular week

What date of the current month does the third Wednesday fall on? That is
a typical question that T-SQL can answer. Verifying date information is
so important because there are so many dates within a database: stop
date, start date, modified date, due date, payment date, posted date, 
etc. The CASE function is useful for determining the date based on certain
criteria.

Using a specified criterion, the CASE function will branch to a criteria 
result gleaned from a potential criteria result set. This is similar to
IF conditional branching, but the CASE function is more eloquent.

The following sample code demonstrates how the third Wednesday of the
current month is determined using the T-SQL CASE statement:

DECLARE @inDate DateTime,
   @WhatWeek int,      -- 0=1stWeek 7=2ndWeek 14=3rdWeek 21=4thWeek
   @WhatDay int       -- 1=Sun 2=Mon 3=Tues 4=Wed 5=Thur 6=Fri 7=Sat

--CAN MANIPULATE TO GET RESULTS
SET @inDate = DATEADD(mm,0,CURRENT_TIMESTAMP)
SET @WhatWeek = 14
SET @WhatDay = 4

--BASED ON THE FIRST DAY OF THE MONTH
SELECT
CASE DATENAME(dw,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1')
)
WHEN 'MONDAY'   THEN DATEADD(dd,
   CASE @WhatDay - 2
   WHEN -1 THEN 6
   WHEN -2 THEN 5
   WHEN -3 THEN 4
   WHEN -4 THEN 3
   WHEN -5 THEN 2
   WHEN -6 THEN 1  
   ELSE @WhatDay - 2 END + @WhatWeek,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1') )
WHEN 'TUESDAY'   THEN DATEADD(dd,
   CASE @WhatDay - 3
   WHEN -1 THEN 6
   WHEN -2 THEN 5
   WHEN -3 THEN 4
   WHEN -4 THEN 3
   WHEN -5 THEN 2
   WHEN -6 THEN 1  
   ELSE @WhatDay - 3 END + @WhatWeek,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1') )
WHEN 'WEDNESDAY' THEN DATEADD(dd,
   CASE @WhatDay - 4
   WHEN -1 THEN 6
   WHEN -2 THEN 5
   WHEN -3 THEN 4
   WHEN -4 THEN 3
   WHEN -5 THEN 2
   WHEN -6 THEN 1  
   ELSE @WhatDay - 4 END + @WhatWeek,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1') )
WHEN 'THURSDAY' THEN DATEADD(dd,
   CASE @WhatDay - 5
   WHEN -1 THEN 6
   WHEN -2 THEN 5
   WHEN -3 THEN 4
   WHEN -4 THEN 3
   WHEN -5 THEN 2
   WHEN -6 THEN 1  
   ELSE @WhatDay - 5 END + @WhatWeek,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1') )
WHEN 'FRIDAY'   THEN DATEADD(dd,
   CASE @WhatDay - 6
   WHEN -1 THEN 6
   WHEN -2 THEN 5
   WHEN -3 THEN 4
   WHEN -4 THEN 3
   WHEN -5 THEN 2
   WHEN -6 THEN 1  
   ELSE @WhatDay - 6 END + @WhatWeek,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1') )
WHEN 'SATURDAY' THEN DATEADD(dd,
   CASE @WhatDay - 7
   WHEN -1 THEN 6
   WHEN -2 THEN 5
   WHEN -3 THEN 4
   WHEN -4 THEN 3
   WHEN -5 THEN 2
   WHEN -6 THEN 1  
   ELSE @WhatDay - 7 END + @WhatWeek,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1') )
WHEN 'SUNDAY'   THEN DATEADD(dd,
   CASE @WhatDay - 1
   WHEN -1 THEN 6
   WHEN -2 THEN 5
   WHEN -3 THEN 4
   WHEN -4 THEN 3
   WHEN -5 THEN 2
   WHEN -6 THEN 1  
   ELSE @WhatDay - 1 END + @WhatWeek,
CONVERT(DATETIME ,
CONVERT(CHAR(4),YEAR(@inDate)) + ' ' +
CONVERT(VARCHAR(35),DATENAME(m, @inDate)) + ' ' +
'1') )
ELSE '1900-01-01'
END

The first part of the code, the declaration section, asks for input to
set the variables: date, week, and day. The next section initializes 
the three variables. The first variable for the incoming date can be 
modified using the DATEADD function. The variable for what week is initialized 
to find the third week. The last variable is initialized to find the
Wednesday of the week. Based on those variables, the CASE statement 
will calculate which date the third Wednesday will be for the month of the 
input date.

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