Search code examples
sqlsql-serverdatesql-server-2000dateadd

SQL Query Help: Transforming Dates In A Non-Trivial Way

I have a table with a "Date" column, and I would like to do a query that does the following:

If the date is a Monday, Tuesday, Wednesday, or Thursday, the displayed date should be shifted up by 1 day, as in 

DATEADD(day, 1, [Date])
On the other hand, if it is a Friday, the displayed date should be incremented by 3 days (i.e. so it becomes the following Monday).

How do I do this in my SELECT statement? As in,

SELECT somewayofdoingthis([Date]) FROM myTable

(This is SQL Server 2000.)

Solution

  • Here is how I would do it. I do recommend a function like above if you will be using this in other places.

    CASE
WHEN
    DATEPART(dw, [Date]) IN (2,3,4,5)
THEN
    DATEADD(d, 1, [Date])
WHEN
    DATEPART(dw, [Date]) = 6
THEN
    DATEADD(d, 3, [Date])
ELSE
    [Date]
END AS [ConvertedDate]