SQL Query Help: Converting Dates in a Nontrivial Direction

Question

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

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

DATEADD (day, 1, [Date])

On the other hand, if it is Friday , the displayed date should be increased by 3 days (i.e. it will be next Monday).

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

SELECT somewayofdoingthis ([Date]) FROM myTable

(This is SQL Server 2000.)

Answer 1

. , , .

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]

Answer 2

CREATE FUNCTION dbo.GetNextWDay(@Day datetime)
RETURNS DATETIME
AS
BEGIN 
    DECLARE @ReturnDate DateTime

    set @ReturnDate = dateadd(dd, 1, @Day)

    if (select datename(@ReturnDate))) = 'Saturday'
        set @ReturnDate = dateadd(dd, 2, @ReturnDate)

    if (select datename(@ReturnDate) = 'Sunday'
        set @ReturnDate = dateadd(dd, 1, @ReturnDate)

    RETURN @ReturnDate
END

Answer 3

Try

select case  when datepart(dw,[Date]) between 2 and 5 then DATEADD(dd, 1, [Date])
when datepart(dw,[Date]) = 6 then DATEADD(dd, 3, [Date]) else [Date] end as [Date] 

Answer 4

, , . , 1 (1,2,3,4,5) when.

case
   --Sunday thru Thursday are shifted forward 1 day
   when datepart(weekday, [Date]) in (1,2,3,4,5) then dateadd(day, 1, [Date]) 
   --Friday is shifted forward to Monday
   when datepart(weekday, [Date]) = 6  then dateadd(day, 3, [Date])
   --Saturday is shifted forward to Monday
   when datepart(weekday, [Date]) = 7  then dateadd(day, 2, [Date])
end

:

select dateadd(day, 1 + (datepart(weekday, [Date])/6) * (8-datepart(weekday, [Date])), [Date])

Answer 5

CASE. SQL Server, :

CASE
  WHEN [Date] is a Friday THEN DATEADD( day, 3, [Date] )
  ELSE DATEADD( day, 1, [Date] )
END

, WHEN- ELSE.

Answer 6

, :

select case when DATENAME(dw, [date]) = 'Monday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Tuesday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Wednesday' then DATEADD(dw, 1, [Date])
                when DATENAME(dw, [date]) = 'Thursday' then DATEADD(dw, 1, [Date])
                when  DATENAME(dw, [date]) = 'Friday' then DATEADD(dw, 3, [Date])
          end as nextDay
    ...

Answer 7

:

select dayname,newdayname =
    CASE dayname
    WHEN 'Monday' THEN 'Tuesday'
    WHEN 'Tuesday' THEN 'Wednesday'
    WHEN 'Wednesday' THEN 'Thursday'
    WHEN 'Thursday' THEN 'Friday'
    WHEN 'Friday' THEN 'Monday'
    WHEN 'Saturday' THEN 'Monday'
    WHEN 'Sunday' THEN 'Monday'
END
FROM UDO_DAYS

results:
Monday       Tuesday
Tuesday      Wednesday
Wednesday    Thursday
Thursday     Friday
Friday       Monday
Saturday     Monday
Sunday       Monday

table data:
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Answer 8

CASE DATEPART. dw DATEPART, , .

Answer 9

. DW . A , ( "MON", "Monday", "22 1998 " ) , . , , .

, , . , , .

DW ( , ), .

Data Dimension Toolkit CSV .

Answer 10

SQL-, .

Answer 11

It looks more like Brian, except that it did not compile due to inconsistent partners, and I changed the IF so that there was no choice in it. It is important to note that we use DateNAME here, not datePART, because datePART depends on the value given by SET DATEFIRST, which sets the first day of the week.

CREATE FUNCTION dbo.GetNextWDay(@Day datetime)
RETURNS DATETIME
AS
BEGIN
    DECLARE @ReturnDate DateTime

    set @ReturnDate = dateadd(dd, 1, @Day)
    if datename(dw, @ReturnDate) = 'Saturday'
        set @ReturnDate = dateadd(dd, 2, @ReturnDate)
    if datename(dw, @ReturnDate) = 'Sunday'
        set @ReturnDate = dateadd(dd, 1, @ReturnDate)
    RETURN @ReturnDate
END

Answer 12

create table #dates (dt datetime)
insert into #dates (dt) values ('1/1/2001')
insert into #dates (dt) values ('1/2/2001')
insert into #dates (dt) values ('1/3/2001')
insert into #dates (dt) values ('1/4/2001')
insert into #dates (dt) values ('1/5/2001')

    select
        dt, day(dt), dateadd(dd,1,dt)
    from
        #dates
    where
        day(dt) between 1 and 4

    union all

    select
        dt, day(dt), dateadd(dd,3,dt)
    from
        #dates
    where
        day(dt) = 5

    drop table #dates