I have a 3 input
Start Time (24 Hr Format: HH:mm:ss)
End Time (24 Hr Format: HH:mm:ss)
From Date (Format yyyy-mm-dd )
How can i perform a function to output below scenario:-
Example 1:
From Date: 2019-07-30
Start Time: 00:00:00
End Time: 05:00:00
Output:
Start Date will be 2019-07-30 00:00:00
End Date will be 2019-07-30 05:00:00
Example 2
From Date: 2019-07-30
Start Time: 16:00:00
End Time: 01:00:00
Output:
Start Date will be 2019-07-30 16:00:00
End Date will be 2019-07-31 01:00:00
Example 3
From Date: 2019-07-30
Start Time: 09:00:00
End Time: 22:00:00
Output:
Start Date will be 2019-07-30 09:00:00
End Date will be 2019-07-30 22:00:00
I manage to do it:
Alter procedure sp_calculateStartEndDate( @Date nvarchar(10), @fromTime nvarchar(8), @toTime nvarchar(8)
--exec sp_calculateStartEndDate '2019-07-30','17:30:00','00:00:01'
)
As
BEGIN
select
startS,
endS,
[Start]=varDate+' '+@fromTime,
[End]=case when cast(@fromTime as Time)>cast('05:00:00' as Time) and cast(@toTime as Time) between cast('12:00:00' as Time) and cast('23:59:59' as Time) then varDate+' '+endTime
when cast(@fromTime as Time)>cast('05:00:00' as Time) and cast(@toTime as Time)=cast('00:00:00' as Time) then varDate+' '+endTime
else
DateAdd(day,1,varDate)+' '+endTime
end
from (
SELECT @Date as varDate,
CONVERT(varchar(15), CAST(@fromTime AS TIME), 100) as startTime,
CONVERT(varchar(15), CAST(@toTime AS TIME), 100) endTime,
RIGHT(CONVERT(varchar(15), CAST(@fromTime AS TIME), 100),2) as startS,
RIGHT(CONVERT(varchar(15), CAST(@toTime AS TIME), 100),2) endS
)A
When working with dates and times, it is almost always better to use the date and time data types that SQL Server provides. In your example, I would do something like this:
DECLARE
@Date DATETIME = '20190730',
@fromTime TIME = '16:00:00',
@toTime TIME = '01:00:00';
DECLARE
@DaysToAdd INT,
@FromDateAndTime DATETIME;
SET @DaysToAdd = CASE WHEN @toTime < @fromTime THEN 1 ELSE 0 END;
DECLARE
@ToDateAndTimeProvisional DATETIME;
SET @ToDateAndTimeProvisional = @Date + CAST(@toTime AS DATETIME);
SELECT DATEADD(DAY, @DaysToAdd, @ToDateAndTimeProvisional);
In the above, I am showing the steps as separate statements. You can combine all of that into a single statement like shown below:
DECLARE
@Date DATETIME = '20190730',
@fromTime TIME = '16:00:00',
@toTime TIME = '01:00:00';
SELECT DATEADD
(
DAY,
CASE WHEN @toTime < @fromTime THEN 1 ELSE 0 END,
@Date+CAST(@toTime AS DATETIME)
);
Thanks.
But, if the parameter @toTime till 00:00:00 is wrong. I tried modified your code as below:
Output must show End Date as 2019-07-30 00:00:00.000
DECLARE @Date DATETIME = '20190730', @fromTime TIME = '16:00:00', @toTime TIME = '01:00:00';
SELECT
StartDate = @Date+CAST(@fromTime AS DATETIME),
EndDate = DATEADD
(
DAY,
CASE
WHEN @toTime='00:00:00' THEN 0
WHEN @toTime < @fromTime THEN 1
ELSE 0 END,
@Date+CAST(@toTime AS DATETIME)
);
You are absolutely right. I had missed the special case of 00:00:00. You may also want to think about what the behavior should be if the @fromTime and @toTime are both 00:00:00.