Microsoft Dot Net Master

Microsoft Dot Net Master
Microsoft Dot Net Master

Thursday, August 27, 2020

SELECT LAST ITEM IN LISTBOX

 Use the following code but select the element before the last

 LIST_ITEM.TopIndex = LIST_ITEM.Items.Count - 1

Monday, June 22, 2020

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 :




Wednesday, June 17, 2020

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)