Get only Saturday and Sunday dates of month in SQL Server

If you want to generate the data, then a CTE is the way to go

DECLARE

     @MONTH INT

    ,@YEAR INT

;

SET @MONTH = 6;

SET @YEAR = 2020;

WITH CTE AS

(

    SELECT CAST(CAST(@MONTH AS VARCHAR(2)) + '/01/' + CAST(@YEAR AS VARCHAR(4)) AS [Date]) AS DATE

        UNION ALL

    SELECT DATEADD(DAY,1,[Date])

    FROM CTE

    WHERE DATE <= CAST(@MONTH AS VARCHAR(2)) + 

        CASE 

            WHEN @MONTH IN (9,4,6,11) 

                THEN '/30/'

            WHEN @MONTH IN (1,3,5,7,8,10,12) 

                THEN '/31/'

            WHEN @MONTH = 2 AND @YEAR/4.00 = @YEAR/4 

                THEN '/29/'

            ELSE '/28/'

        END

        + CAST(@YEAR AS VARCHAR(4))

)

SELECT 

     [Date]

    ,CASE DATEPART(dw,[Date])

        WHEN 1 THEN 'Sunday'

        WHEN 2 THEN 'Monday'

        WHEN 3 THEN 'Tuesday'

        WHEN 4 THEN 'Wednesday'

        WHEN 5 THEN 'Thursday'

        WHEN 6 THEN 'Friday'

        WHEN 7 THEN 'Saturday'

    END

FROM CTE

WHERE DATEPART(dw,[Date]) IN (1,7)

OPTION (MAXRECURSION 0)

;

Output : 

How can I select the Last day of a month in SQL?

Simple Query:

SELECT     EOMONTH(getdate())

How can I select the first day of a month in SQL?

Simple Query:

SELECT DATEADD(m, DATEDIFF(m, 0, GETDATE()), 0) 

-- Instead of GetDate you can put any date.

How to calculate work days except Saturday and Sunday

Script shows the complete definition for a user defined function that calculates working days by mostly using the DATEDIFF function.

CREATE FUNCTION [dbo].[fn_GetTotalWorkingDays]

(

    @DateFrom Date,

    @DateTo Date

)

RETURNS INT

AS

BEGIN

    DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) + 1;

    DECLARE @TotWeeks INT= DATEDIFF(WEEK, @DateFrom, @DateTo) * 2;

    DECLARE @IsSunday INT= CASE

WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday'

THEN 1

ELSE 0

  END;

    DECLARE @IsSaturday INT= CASE

   WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday'

   THEN 1

   ELSE 0

    END;

    DECLARE @TotWorkingDays INT= @TotDays - @TotWeeks - @IsSunday + @IsSaturday;

    RETURN @TotWorkingDays;

END

 

Example :

DateDiff to output hours and minutes

Very simply:

CONVERT(TIME,Date2 - Date1)

For example:

Declare @Date2 DATETIME = '2016-01-01 10:01:10.022'
Declare @Date1 DATETIME = '2016-01-01 10:00:00.000'
Select CONVERT(TIME,@Date2 - @Date1) as ElapsedTime

Yelds:

ElapsedTime
----------------
00:01:10.0233333

(1 row(s) affected)