SQL Server Read date in SQL [closed]

Closed. This question needs details or clarity . It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post .

Closed 4 days ago.
Improve this question

Why this query calculates the working days corectly, even if the @StartDate=7(Saturday) and @EndDate=1 (Sunday)... because I subtract a day only if I have @StartDate=1(Sunday) and @EndDate=7 (Saturday)

CREATE FUNCTION dbo.WorkingDays (@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @TotalDays INT, @WorkDays INT

    SET @TotalDays = DATEDIFF(DAY, @StartDate, @EndDate) + 1 -- +1(ia in considerare si prima zi)
    SET @WorkDays = @TotalDays - (DATEDIFF(WEEK, @StartDate, @EndDate) * 2)

    -- Excludem zilele de weekend (sâmbătă și duminică)
    IF DATEPART(WEEKDAY, @StartDate) = 1  -- calculeaza  a cata zi din sapt este (1-Duminica)
        SET @WorkDays = @WorkDays - 1
    IF DATEPART(WEEKDAY, @EndDate) = 7  -- a cata zi din sapt este (7-Sambata)
        SET @WorkDays = @WorkDays - 1

    RETURN @WorkDays
END;

-- Exemplu de interogare
DECLARE @StartDate DATE = '2023-07-18'
DECLARE @EndDate DATE = '2023-07-20'

SELECT dbo.WorkingDays(@StartDate, @EndDate) AS numar_zile_lucratoare;

I assume that you are using SQL Server

Let's break down the function step by step:

@TotalDays = DATEDIFF(DAY, @StartDate, @EndDate) + 1: This calculates the total number of days between @StartDate and @EndDate, including both the start and end dates. The +1 is added to ensure that the first day (@StartDate) is also counted.

@WorkDays = @TotalDays - (DATEDIFF(WEEK, @StartDate, @EndDate) * 2): This calculates the total number of weekdays (working days) between @StartDate and @EndDate. It first calculates the total number of weeks between the two dates (using DATEDIFF(WEEK, @StartDate, @EndDate)), and then multiplies it by 2 to account for both Saturday and Sunday of each week. This effectively subtracts all the weekends from the total days.

The following conditional statements adjust the @WorkDays variable to account for the cases when @StartDate or @EndDate falls on a weekend.

a. IF DATEPART(WEEKDAY, @StartDate) = 1: If @StartDate falls on a Sunday (1), it means that the first day is a non-working day. So, @WorkDays is decreased by 1.

b. IF DATEPART(WEEKDAY, @EndDate) = 7: If @EndDate falls on a Saturday (7), it means that the last day is a non-working day. So, @WorkDays is decreased by 1.

Since the calculation handles both cases when @StartDate or @EndDate fall on weekends, the function will correctly calculate the number of working days between the two dates.

For example, if you use @StartDate = '2023-07-18' (which is a Tuesday) and @EndDate = '2023-07-20' (which is a Thursday), the function will return 3, which is the correct number of working days between these dates (Tuesday, Wednesday, and Thursday).

If you are working with MySql, then there is a syntax difference which could cause unexpected results in certain scenarios.

You can Modify existing query to MySql query as follows:

CREATE FUNCTION WorkingDays (StartDate DATE, EndDate DATE)
RETURNS INT
BEGIN
    DECLARE TotalDays INT;
    DECLARE WorkDays INT;

SET TotalDays = DATEDIFF(EndDate, StartDate) + 1; -- +1 to include the start date
SET WorkDays = TotalDays - FLOOR(TotalDays / 7) * 2;

-- Adjust for weekends
IF DAYOFWEEK(StartDate) = 1 THEN
    SET WorkDays = WorkDays - 1;
END IF;
IF DAYOFWEEK(EndDate) = 7 THEN
    SET WorkDays = WorkDays - 1;
END IF;

RETURN WorkDays;
END;

you can use it in your queries to calculate the number of working days correctly, even if the StartDate is a Saturday and EndDate is a Sunday. For example:

SET @StartDate = '2023-07-18';
SET @EndDate = '2023-07-20';
SELECT WorkingDays(@StartDate, @EndDate) AS numar_zile_lucratoare;

This will correctly return 3 as the number of working days between the given dates.

I hope this helps.

SQL Server Read date in SQL [closed]

1条答案

相关问题

热门标签

最新问答