SQLTeam.com | Weblogs | Forums

2 Timespan and 1 date to determine start & end

Hi All,

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

Please advise.

Thanks.

Regards,
Micheale

Hi All,

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

END

Do let me know if my solution is wrong. Thanks.

Regards,
Micheale

hi

i tried to do this

please see how i did it
if it helps great
:slight_smile::slight_smile:

i love any feedback
thanks

sql
declare @FromDate nvarchar(100) ='2019-07-30'
declare @StartTime nvarchar(100) ='00:00:00'
declare @EndTime nvarchar(100) ='05:00:00'

select  'start date', cast( @FromDate + ' '+@StartTime as datetime)
select  'end date',cast( @FromDate + ' '+@EndTime as datetime)
go

image

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)
	);
1 Like

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)
);

Am I did the right way?

Please advise.

Thanks.

Regards,
Micheale

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.

1 Like