SQL Server 2008 - date functions and formulas

I want to return results where if the date falls on 10 & 28 of each month, but if either is a weekend return the result for Friday (first working day before).

Eg. if the following lines to be returned are

10 Oct 2010    Sunday
28 Oct 2010    Thursday

In the table I have

LineId Date
1      08 Oct 2010
2      28 Oct 2010

so, because 10 October is a Su开发者_C百科nday, therefore won't be in the table, it will return LineID 1 as the is first working day before.

Thank you.

DATEPART(WEEKDAY and DATEPART(DW are dependant on the DATEFIRST setting. To avoid incorrect results the @@DATEFIRST function can be used.

WITH T(D) AS
(
SELECT CAST('20111008' AS DATE) UNION ALL
SELECT CAST('20111009' AS DATE) UNION ALL
SELECT CAST('20111010' AS DATE) UNION ALL
SELECT CAST('20111011' AS DATE) UNION ALL
SELECT CAST('20111012' AS DATE) UNION ALL
SELECT CAST('20111013' AS DATE) UNION ALL
SELECT CAST('20111014' AS DATE) 
)
SELECT CASE
         WHEN ( @@DATEFIRST + DATEPART(WEEKDAY, D) ) % 7 > 1 THEN D
         ELSE DATEADD(DAY, -( 1 + ( @@DATEFIRST + DATEPART(WEEKDAY, D) ) % 7 ), D)
       END AS WeekDayOrPrecedingFriday
FROM   T   

Select Case
        When DatePart(dw,SampleData.[Date]) = 1 Then DateAdd(d,-2,SampleData.[Date])
        When DatePart(dw,SampleData.[Date]) = 7 Then DateAdd(d,-1,SampleData.[Date])
        Else SampleData.[Date]
        End
From    (
        Select Cast('2010-10-10' As datetime) As [Date]
        Union All Select '2010-10-28' As [Date]
        ) As SampleData

You may find that it is easier to have a Calendar table with one row for all days you need where you indicate whether the given day is a "working" day. In this way, you can easily account for holidays and the actual day off for holidays (e.g. if July 4th, in the US, is on a Saturday, mark the preceding Friday as a day off.).

If you are really worried about dealing with DateFirst, just set it prior to running your query:

Set DateFirst 7;

The above is the US default setting which is that Sunday is the first day of the week.

I really like to use a calendar table for queries like these.

-- For convenience, I'll use a view. The view "weekdays" is a proper 
-- subset of the table "calendar".
create view weekdays as 
select * from calendar
where day_of_week in ('Mon', 'Tue', 'Wed', 'Thu', 'Fri');

Having done that, the query is not only dead simple, it can easily be seen to be right.

select max(cal_date) 
from weekdays
where cal_date <= '2010-10-10'  -- Returns 2011-10-08

Doesn't account for holidays that might fall on the 10th or 28th, but that's easy enough to remedy.