CODEWITHSUNDEEP
sqlsql-servert-sql
Internet Engineer
Internet Engineer

Reputation: 2534

Move Date to Monday in TSQL

I need to create a function in TSQL that will move the date to Monday if the date passed is either a Saturday or Sunday only.

For example:

If the date is not a Saturday or Sunday, return the input date

Upvotes: 1

Views: 3730

Answers (8)

Rajesh Rajamani
Rajesh Rajamani

Reputation: 531

I extended the solution provided by zimdanen on 8th May as when the start date was a friday it failed .

DECLARE @FromDate DATETIME

BEGIN

SET @FromDate='04/07/2014'

SELECT
     @FromDate as [StartDate]
   , CASE (@@DATEFIRST + DATEPART(dw, (DATEADD(DD,3,@FromDate)))) % 7
        WHEN 0 THEN DATEADD(day, 2, (DATEADD(DD,3,@FromDate))) -- Saturday
        WHEN 1 THEN 
                    CASE    
                        WHEN DATEPART(dw,@FromDate)=5 Then DATEADD(DD,5,@FromDate) -- Thursday
                        ELSE DATEADD(day, 1, (DATEADD(DD,3,@FromDate))) -- Sunday  
                    END                       

        ELSE 
                    CASE    
                        WHEN DATEPART(dw,@FromDate)=6 Then DATEADD(DD,5,@FromDate) -- Friday
                        ELSE DATEADD(DD,3,@FromDate)
                    END 

    END AS Weekday



END

Upvotes: -1

zimdanen
zimdanen

Reputation: 5626

This will give you the Monday following if the date is a Saturday or Sunday; otherwise, it will give you the date you provided.

DECLARE @Date DATETIME; SET @Date = GETDATE();

SELECT
    CASE (@@DATEFIRST + DATEPART(dw, @Date)) % 7
        WHEN 0 THEN DATEADD(day, 2, @Date) -- Saturday
        WHEN 1 THEN DATEADD(day, 1, @Date) -- Sunday
        ELSE @Date
    END AS Weekday

EDIT: Included Conrad Frix's solution to the @@DATEFIRST issue.

Upvotes: 8

Tim Schmelter
Tim Schmelter

Reputation: 460058

Use a combination of DATEPART and DATEADD:

select  date
,       CASE DATEPART(weekday, date)            
            WHEN 6 THEN DATEADD(day, 2, date) -- Saturday
            WHEN 7 THEN DATEADD(day, 1, date) -- Sunday
            ELSE date 
        END AS NewDate
from @dates
order by date

Sample data:

declare @dates table(date datetime);
insert into @dates values('20120401');
insert into @dates values('20120402');
insert into @dates values('20120403');
insert into @dates values('20120404');
insert into @dates values('20120405');
insert into @dates values('20120406');
insert into @dates values('20120407');
insert into @dates values('20120408');
insert into @dates values('20120409');
insert into @dates values('20120410');

Result:

date                    NewDate
2012-04-01 00:00:00.000 2012-04-02 00:00:00.000
2012-04-02 00:00:00.000 2012-04-02 00:00:00.000
2012-04-03 00:00:00.000 2012-04-03 00:00:00.000
2012-04-04 00:00:00.000 2012-04-04 00:00:00.000
2012-04-05 00:00:00.000 2012-04-05 00:00:00.000
2012-04-06 00:00:00.000 2012-04-06 00:00:00.000
2012-04-07 00:00:00.000 2012-04-09 00:00:00.000
2012-04-08 00:00:00.000 2012-04-09 00:00:00.000
2012-04-09 00:00:00.000 2012-04-09 00:00:00.000
2012-04-10 00:00:00.000 2012-04-10 00:00:00.000

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

A version that is not depending on set datefirst

select dateadd(day,
               case datediff(day, '17530101', @D) % 7
                 when 5 then 2
                 when 6 then 1
                 else 0 
               end,
               @D)

Same thing perhaps prettier.

select case datediff(day, '17530101', @D) % 7 
         when 5 then dateadd(day, 2, @D) 
         when 6 then dateadd(day, 1, @D) 
         else @D 
       end

The constant 17530101 is a Monday and works for datetime. If you are using date you can use 00010101 instead.

Upvotes: 2

Dan P
Dan P

Reputation: 1999

This should do it for you. Basically adds 2 days on Saturday or 1 day on Sunday to move the sample date to Monday

DECLARE @Temp TABLE
(
    dtSample DATETIME
)

INSERT INTO @Temp VALUES ('5/1/2012')
INSERT INTO @Temp VALUES ('5/2/2012')
INSERT INTO @Temp VALUES ('5/3/2012')
INSERT INTO @Temp VALUES ('5/4/2012')
INSERT INTO @Temp VALUES ('5/5/2012')
INSERT INTO @Temp VALUES ('5/6/2012')
INSERT INTO @Temp VALUES ('5/7/2012')
INSERT INTO @Temp VALUES ('5/8/2012')
INSERT INTO @Temp VALUES ('5/9/2012')
INSERT INTO @Temp VALUES ('5/10/2012')
INSERT INTO @Temp VALUES ('5/11/2012')
INSERT INTO @Temp VALUES ('5/12/2012')
INSERT INTO @Temp VALUES ('5/13/2012')
INSERT INTO @Temp VALUES ('5/14/2012')

SELECT 
    dtSample
    ,CASE WHEN DATEPART(WEEKDAY, dtSample) = 7 
        THEN dtSample + 2
        ELSE 
            CASE WHEN DATEPART(WEEKDAY, dtSample) = 1
                THEN dtSample + 1
                ELSE dtSample
            END
     END AS dtConverted             
FROM @Temp

Results:

dtSample    dtConverted
2012-05-01 00:00:00.000 2012-05-01 00:00:00.000
2012-05-02 00:00:00.000 2012-05-02 00:00:00.000
2012-05-03 00:00:00.000 2012-05-03 00:00:00.000
2012-05-04 00:00:00.000 2012-05-04 00:00:00.000
2012-05-05 00:00:00.000 2012-05-07 00:00:00.000
2012-05-06 00:00:00.000 2012-05-07 00:00:00.000
2012-05-07 00:00:00.000 2012-05-07 00:00:00.000
2012-05-08 00:00:00.000 2012-05-08 00:00:00.000
2012-05-09 00:00:00.000 2012-05-09 00:00:00.000
2012-05-10 00:00:00.000 2012-05-10 00:00:00.000
2012-05-11 00:00:00.000 2012-05-11 00:00:00.000
2012-05-12 00:00:00.000 2012-05-14 00:00:00.000
2012-05-13 00:00:00.000 2012-05-14 00:00:00.000
2012-05-14 00:00:00.000 2012-05-14 00:00:00.000

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

Depending upon your SET DATEFIRST options (US English default is 7, for Sunday = 1):

CASE
    WHEN DATEPART(weekday, @dt) = 1 -- Sunday
        THEN DATEADD(day, 1, @dt)
    WHEN DATEPART(weekday, @dt) = 7 -- Saturday
        THEN DATEADD(day, 2, @dt)
    ELSE
        @dt 
END

Upvotes: 2

Carl Sharman
Carl Sharman

Reputation: 4845

How about this:

SELECT CASE datepart(dw, @date) WHEN 7 THEN @date + 2 WHEN 1 THEN @date + 1 ELSE @date END

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48402

You can use the DatePart function in T-SQL. For each date, you can extract day component, and if it's a Saturday, increment the date by two days. If it's Sunday, increment the date by one day. You can use DateAdd to increment a date.

Upvotes: 2

Related Questions