Get the Date time between two dates.

Question

I have two dates like '01-14-18 09:00:00 PM' and 01-15-18 08:30:00 AM

 

Now I want to get the table with columns like

 

DayName          StartTime            EndTime 

 Sunday             09:00:00             23.59.59

MonDay             00:00:00             08.30.00

 

 

Means I want a total time in each day.

 

Please, guys, help me. 

 

 

Answer 1

Hello 

 

Find folowing snippet as per your provided date :

 

--For total year difference
ELECT DATEDIFF(year, '2018/01/14', '2018/01/15') AS Year;
--For total Months difference
ELECT DATEDIFF(MONTH, '2018/01/14', '2018/01/15') AS Months;
--For total Days difference
ELECT DATEDIFF(DAY, '2018/01/14', '2018/01/15') AS Days;
--For total Hour difference
ELECT DATEDIFF(HOUR, '2018/01/14', '2018/01/15') AS Hour;
--For total Minutes difference
ELECT DATEDIFF(MINUTE, '2018/01/14', '2018/01/15') AS Minute;
--For total Seconds difference
ELECT DATEDIFF(SECOND, '2018/01/14', '2018/01/15') AS Second;
--For total Seconds difference
ELECT DATEDIFF(MILLISECOND, '2018/01/14', '2018/01/15') AS Milliseconds;

Thanks 

Answer 2

Hello,

Try This :

 

DECLARE @FromDate DATETIME, @ToDate DATETIME, @TotalDays INT, @intI INT
DECLARE @TimeTable TABLE (DAY VARCHAR(50), START_TIME VARCHAR(50), END_TIME VARCHAR(50))


SET @FromDate = '2018-01-09 10:12:00.000'
SET @ToDate = '2018-01-12 16:04:51.000'
SET @TotalDays = DATEDIFF(DAY,@FromDate,@ToDate)+1
SET @intI=0

WHILE (@intI < @TotalDays)
BEGIN

INSERT INTO @TimeTable( DAY, START_TIME, END_TIME )

SELECT DATENAME(WEEKDAY,DATEADD(DAY,@intI,@FromDate))
, CASE WHEN @intI=0 THEN CONVERT(VARCHAR(8), @FromDate, 108) ELSE '00:00:00' END
, CASE WHEN @intI<>@TotalDays-1 THEN '23:59:59' ELSE CONVERT(VARCHAR(8), @ToDate, 108) END


SET @intI = @intI + 1
END

SELECT * FROM @TimeTable