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

date, dateadd, sql, sql-server, 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]

Problem

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.)

Original source