How to get last date of month, but hide Sunday and Saturday in SQL Server

Please help.. this my code

DECLARE @StartDT DATE = '2018-03-01'
DECLARE @Tanggal AS DATE = '2018-03-01'
DECLARE @EndDT DATE = '2018-03-31'
DECLARE @Hari AS INT

--DECLARE @AkhirBulan AS DATE

WHILE @StartDT <= @EndDT
BEGIN
SET @Hari = DATEPART(dw,@StartDT)
--SET @EndDT = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@EndDT)+1,0))

IF @Hari NOT IN (7,1)
AND @EndDT IN (DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @EndDT) + 1, 0)))
BEGIN
IF NOT EXISTS(SELECT * FROM dbo.Holiday
WHERE HolidayDate = @StartDT)
--IF @EndDT IN (@EndDT) PRINT CONVERT(VARCHAR,@StartDT)--+'-'+CONVERT(VARCHAR(3),@hari ) --EXEC
DashboardHistory_Insert @Tanggal, @StartDT
END

SET @StartDT = DATEADD(DAY, 1, @StartDT)
--SELECT EOMONTH ( @StartDT ) END


This is the result of my script

2018-03-01 2018-03-02 2018-03-05 2018-03-06 2018-03-07 2018-03-08 2018-03-09 2018-03-12 2018-03-13 2018-03-14 2018-03-15 2018-03-16 2018-03-19 2018-03-20 2018-03-21 2018-03-22 2018-03-23 2018-03-26 2018-03-27 2018-03-28 2018-03-29 2018-03-30


2018-03-31 <- I want to get last of month like this but sunday and saturday not showing

MySQL

SQL Server

2 Answers
2

A far better approach to this would be to not use a WHILE on something that can be easily achieved using a set based method. Using a WHILE or CURSOR is normally an awful idea for something that can be completely by a set based method, as they are slow and scale poorly.

WHILE

WHILE

CURSOR

One way of doing this would be to use a Tally, and then build your data set on that. For example:

USE Sandbox;
GO

DECLARE @StartDate date, @EndDate date;
SET @StartDate = '20180301';
SET @EndDate = '20180331';

WITH CTE AS (
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) V(N)),
Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
FROM CTE C1
CROSS JOIN CTE C2),
Dates AS(
SELECT DATEADD(DAY,T.I, @StartDate) AS CalendarDate,
ROW_NUMBER() OVER (ORDER BY T.I DESC) AS Ir
FROM Tally T
WHERE T.I <= DATEDIFF(DAY, @StartDate, @EndDate))
SELECT D.CalendarDate, DATENAME(WEEKDAY,D.CalendarDate) AS CalendarDay
FROM Dates D
WHERE DATENAME(WEEKDAY,D.CalendarDate) NOT IN ('Saturday','Sunday') --Note this is language specific
OR Ir = 1
ORDER BY D.CalendarDate ASC;


If you want to exclude all the Sundays and Saturdays except if it is the last day of the month then below query will give you the result. Please check and alter the SELECT statement accordingly.

DECLARE @StartDT DATE='2018-03-01'
DECLARE @EndDT DATE='2018-03-31'

WHILE @StartDT <= @EndDT
BEGIN
IF (DATEPART(DW,@StartDT) = 1 OR DATEPART(DW,@StartDT) = 7 ) AND (EOMONTH(@StartDT)!= @StartDT)
BEGIN
SET @StartDT = DATEADD(D,1,@StartDT)
END
ELSE
BEGIN
SELECT @StartDT
END
SET @StartDT = DATEADD(D,1,@StartDT)
END


WHILE

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.