Select only business day query bug

Question

I have the following query running against a Sybase database which is intended to select the two previous business days; ignoring weekends (we don't care about holidays). The query works, apart from on mondays, in which the query only returns the date for the previous friday - running the query on a tuesday works, returning monday and the previous friday. I've been staring at it all day and can't work out why; can anybody help? Unfortunately in this situation I am unable to use a calendar table.

SELECT dateadd(dd, CASE DATEPART(dw, getdate())
WHEN 1 THEN -2
WHEN 2 THEN -3 ELSE -1
END, CONVERT (VARCHAR (10), dateadd(day, 0 getdate()), 102)), (SELECT dateadd(dd, CASE DATEPART(dw, CONVERT (VARCHAR (10), dateadd(day, -1, getdate()), 102))
WHEN 1 THEN -2
WHEN 2 THEN -3 ELSE -1
END, CONVERT (VARCHAR (10), dateadd(day, -1, getdate()), 102)))

Any assistance would be greatly appreciated!

Which Sybase database? There are 3 different ones: SQLAnywhere (ASA), Adaptive Server Enterprise (ASE) or IQ — frlan, Sep 16 '14 at 19:25

jpw · Answer 1 · 2014-09-16T19:42:45.207

I think maybe your logic was a bit flawed (and possibly unnecessarily complex).

I tested this (with set datefirst 1 for Monday as the first day of the week) and it returned the correct dates for Mon through Fri.

The logic says to deduct 4 days if Monday or Tuesday (so Mon -> Thu and Tue -> Fri), otherwise 2 for the oldest date, and deduct 3 days if Mon for the second oldest (so Mon -> Fri) otherwise deduct 1 day.

If your week doesn't start on Monday but maybe Sunday (as the default US setting) you'll have to adjust the deductions accordingly.

Try this:

SELECT 
DATEADD(
    dd, 
    CASE DATEPART(dw, GETDATE())
       WHEN 1 THEN -4
       WHEN 2 THEN -4
       ELSE -2
    END, 
    CONVERT (VARCHAR (10), dateadd(day, 0 ,GETDATE()), 102)
    ) AS [Two days previous]
    , 
DATEADD(
    dd, 
    CASE DATEPART(dw, GETDATE()) 
       WHEN 1 THEN -3    
       ELSE -1
    END, 
    CONVERT (VARCHAR (10), DATEADD(DAY, 0, GETDATE()), 102)
    ) AS [One day previous]

Sample SQL Fiddle (using MS SQL, but in this regard I believe it's functionally similar enough to Sybase).

Select only business day query bug

1 Answers1