June 29, 2017 at 6:00 am
You'd think this would be simple. And it is, except I'm wondering if I'm making this too complicated... I need the next future Friday for a situation, and then I need to convert it to a character date (no symbols, YYYYMMDD format) for a feed I'm doing. So I came up with the below CASE statement, but again, I'm wondering if there's an easier way to do this. Thoughts?
SELECT REPLACE(CONVERT(VARCHAR(10),CASE WHEN DATEPART(dw,GETDATE()) = 6 THEN DATEADD(d,7,CONVERT(DATE,GETDATE()))
WHEN DATEPART(dw,GETDATE()) = 7 THEN DATEADD(d,6,CONVERT(DATE,GETDATE()))
WHEN DATEPART(dw,GETDATE()) = 1 THEN DATEADD(d,5,CONVERT(DATE,GETDATE()))
WHEN DATEPART(dw,GETDATE()) = 2 THEN DATEADD(d,4,CONVERT(DATE,GETDATE()))
WHEN DATEPART(dw,GETDATE()) = 3 THEN DATEADD(d,3,CONVERT(DATE,GETDATE()))
WHEN DATEPART(dw,GETDATE()) = 4 THEN DATEADD(d,2,CONVERT(DATE,GETDATE()))
WHEN DATEPART(dw,GETDATE()) = 5 THEN DATEADD(d,1,CONVERT(DATE,GETDATE()))
END),'-','');
BTW, our week starts on Sunday (DATEPART(dw) = 1). So Friday would be 6.
June 29, 2017 at 6:35 am
DECLARE @NextDayID INT = 4 -- 0=Mon, 1=Tue, 2 = Wed, ..., 5=Sat, 6=Sun
SELECT CONVERT(varchar(8),DATEADD(DAY, (DATEDIFF(DAY, @NextDayID, GETDATE()) / 7) * 7 + 7, @NextDayID),112)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 29, 2017 at 6:56 am
J Livingston SQL - Thursday, June 29, 2017 6:35 AMfrom https://stackoverflow.com/questions/18920393/sql-server-get-next-relative-day-of-week-next-monday-tuesday-wed
DECLARE @NextDayID INT = 4 -- 0=Mon, 1=Tue, 2 = Wed, ..., 5=Sat, 6=Sun
SELECT CONVERT(varchar(8),DATEADD(DAY, (DATEDIFF(DAY, @NextDayID, GETDATE()) / 7) * 7 + 7, @NextDayID),112)
HA! I knew there had to be a way... Thanks.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply