hi,
i have this sql script how show me due date résult when i execute it in sql:
i wanna show this result (due_date) in text box windows form
can you help me please
i have this sql script how show me due date résult when i execute it in sql:
SQL:
SET language british;
DECLARE @business_hours TABLE
(
work_day VARCHAR(10),
open_time VARCHAR(8),
close_time VARCHAR(8)
)
INSERT INTO @business_hours
VALUES ('Monday',
'08:30:00',
'17:00:00')
INSERT INTO @business_hours
VALUES ('Tuesday',
'08:30:00',
'17:00:00')
INSERT INTO @business_hours
VALUES ('Wednesday',
'08:30:00',
'17:00:00')
INSERT INTO @business_hours
VALUES ('Thursday',
'08:30:00',
'17:00:00')
INSERT INTO @business_hours
VALUES ('Friday',
'08:30:00',
'18:00:00')
INSERT INTO @business_hours
VALUES ('Saturday',
'09:00:00',
'14:00:00')
DECLARE @holidays TABLE
(
holiday VARCHAR(10)
)
INSERT INTO @holidays
VALUES ('02-02-2021')
INSERT INTO @holidays
VALUES ('03-02-2021')
DECLARE @start_date DATETIME = '01-02-2021 16:12:47'
DECLARE @time_span INT = 970-- time till due in minutes
DECLARE @true BIT = 'true'
DECLARE @false BIT = 'false'
DECLARE @due_date DATETIME --our output
--other variables
DECLARE @date_string VARCHAR(10)
DECLARE @today_closing DATETIME
DECLARE @is_workday BIT = @true
DECLARE @is_holiday BIT = @false
SET @start_date = Dateadd(ss, Datepart(ss, @start_date) *- 1, @start_date)
WHILE ( @time_span > 0 )
BEGIN
SET @due_date = Dateadd(minute, @time_span, @start_date)
SET @date_string = Format(Dateadd(dd, 0, Datediff(dd, 0, @start_date)),
'dd-MM-yyyy')
SET @today_closing = (SELECT CONVERT(DATETIME, @date_string + ' ' +
close_time)
FROM @business_hours
WHERE work_day = Datename(weekday, @start_date))
IF EXISTS((SELECT work_day
FROM @business_hours
WHERE work_day = Datename(weekday, @start_date)))
SET @is_workday = @true
ELSE
SET @is_workday = @false
IF EXISTS(SELECT holiday
FROM @holidays
WHERE holiday = @date_string)
SET @is_holiday = @true
ELSE
SET @is_holiday = @false
IF @is_workday = @true
AND @is_holiday = @false
BEGIN
IF @due_date > @today_closing
SET @time_span = @time_span - Datediff(minute, @start_date,
@today_closing
)
ELSE
SET @time_span = @time_span - Datediff(minute, @start_date,
@due_date)
END
SET @date_string = Format(Dateadd(dd, 1, Datediff(dd, 0, @start_date)),
'dd-MM-yyyy')
SET @start_date = CONVERT(DATETIME, @date_string + ' '
+ Isnull((SELECT open_time FROM
@business_hours WHERE work_day
= Datename(weekday,
CONVERT(DATETIME,
@date_string))), '')
)
END
SELECT @due_date
i wanna show this result (due_date) in text box windows form
can you help me please
Last edited by a moderator: