Finding Closest future date

Question

Is it possible to find the closest future date (datetime) by a date varchar value?

Given,

DECLARE @DayValue VARCHAR(3)
 , @DateValue DATETIME 
SET @DayValue = 'Tue' -- Values could be 'Mon', 'Tue', 'Wed' and etc. 
SET @DateValue = '10/15/2014' -- Format is MM/dd/yyyy

I want to get:

Oct 21 2014 12:00AM

Answer 1

This is a bit chunky solution, but it works. :)

SET DATEFIRST 1
DECLARE @DateValue DateTime
 , @DayValue VARCHAR(3)
 , @tmp INT
SET @DateValue = '09/30/2014'
SET @DayValue = 'wed'
SET @tmp = CASE @DayValue
    WHEN 'Mon' THEN (1 - DATEPART(dw, @DateValue) + 7) % 7
    WHEN 'Tue' THEN (2 - DATEPART(dw, @DateValue) + 7) % 7
    WHEN 'Wed' THEN (3 - DATEPART(dw, @DateValue) + 7) % 7
    WHEN 'Thu' THEN (4 - DATEPART(dw, @DateValue) + 7) % 7
    WHEN 'Fri' THEN (5 - DATEPART(dw, @DateValue) + 7) % 7
    WHEN 'Sat' THEN (6 - DATEPART(dw, @DateValue) + 7) % 7
    WHEN 'Sun' THEN (7 - DATEPART(dw, @DateValue) + 7) % 7
END
SELECT 
 CASE 
    WHEN @tmp = 0 THEN DATEADD (DAY, 7, @DateValue)
    ELSE DATEADD (DAY, @tmp, @DateValue)
   END

Answer 2

If you could define DayValue as an integer, you solve this problem with more elegant way:

DECLARE @DayValue int, @DateValue DATETIME 
SET @DayValue = 3 -- Values could be 1-Sun, 2-Mon, 3-Tue, 4-Wed and etc. 
SET @DateValue = '10/15/2014' -- Format is MM/dd/yyyy

select dateadd(day,(7 + @DayValue - datepart(w,@DateValue)), @DateValue)

TRY SQL FIDDLE DEMO

Answer 3

You could use this function if you had a date-table:

CREATE FUNCTION [dbo].[GetNextDayOfWeek] 
(   @DayOfWeek      VARCHAR(3),
    @DateValue     datetime
)
RETURNS SmallDateTime 
AS
BEGIN
    DECLARE @NextDayOfWeek smalldatetime
    SET @NextDayOfWeek = (
    SELECT 
        MIN(d.Date) 
    FROM 
        tDefDate d
    WHERE 
        d.Date > @DateValue
    AND LEFT(DATENAME(Weekday, d.Date), 3) = @DayOfWeek);
    RETURN @NextDayOfWeek
END

Then it's simple as:

select [dbo].[GetNextDayOfWeek]('Tue', Getdate()) -- next tuesday=> 2014-10-21

Note that it takes the language of the database into account. So if it's in german:

select [dbo].[GetNextDayOfWeek]('Die', Getdate()) -- next tuesday(Dienstag)

---

Here's a version that works also without a date-table (but is less efficient).

CREATE FUNCTION [dbo].[GetNextDayOfWeek] 
(   @DayOfWeek      VARCHAR(3),
    @DateValue     datetime
)
RETURNS SmallDateTime 
AS
BEGIN
    DECLARE @NextDayOfWeek smalldatetime

    ;WITH CTE as
    (
        SELECT GetDate() DateValue, DayNum=0

        UNION ALL

        SELECT DateValue + 1, DayNum=DayNum+1
        FROM  CTE
        WHERE DayNum <=7 
    )
    SELECT @NextDayOfWeek = (
        SELECT 
            MIN(d.DateValue) 
        FROM 
            CTE d
        WHERE d.DateValue > @DateValue
        AND   LEFT(DATENAME(Weekday, d.DateValue), 3) = @DayOfWeek
    )OPTION (MAXRECURSION 8);
    RETURN @NextDayOfWeek
END

Answer 4

No loops and will work in selects with multiple rows. :)

DECLARE @DayValue CHAR(3)
DECLARE @DateValue DATETIME
DECLARE @FutureDate DATE
SET @DayValue='MON'
SET @DateValue='10/12/2014'

DECLARE @Days TABLE
(
    [DayOfWeek] TINYINT,
    [DayValue] CHAR(3)
)

INSERT INTO @Days([DayOfWeek],[DayValue])
    SELECT 0,'SUN' UNION
    SELECT 1,'MON' UNION
    SELECT 2,'TUE' UNION
    SELECT 3,'WED' UNION
    SELECT 4,'THU' UNION
    SELECT 5,'FRI' UNION
    SELECT 6,'SAT'


SET @FutureDate=
    DATEADD(DAY,
        --Skip to next week if we are already on the desired day or past it
        + CASE WHEN ((SELECT [DayOfWeek] FROM @Days WHERE [DayValue]=@DayValue)<DATEPART(WEEKDAY,@DateValue)) THEN 7 ELSE 0 END

        --reset to start of week (add one as DATEPART is base 1, not base 0)
        - DATEPART(WEEKDAY,@DateValue) + 1

        --Add the desired day of the week
        + (SELECT [DayOfWeek] FROM @Days WHERE [DayValue]=@DayValue)

        ,@DateValue)

SELECT @FutureDate

Answer 5

Using Loop,

    DECLARE @DayValue VARCHAR(3) 
            ,@DateValue DATETIME 
    SET     @DayValue = 'tue'
    SET     @DateValue = '10/15/2014'
    declare @i  int= 1  ,@day varchar(3) = null
    while (@i<=7 )
        begin 

        Select @day = left(datename (dw,@DateValue),3)

            if  @day = @DayValue
                begin 
                    Select  @DateValue
                    break
                end 

        Select @DateValue = @DateValue+ 1

        Select @i = @i+1

        end