Loading

Get the Date time between two dates.

Get the Date time between two dates.

SreeRam Kondagorla

SreeRam Kondagorla

8y932SQL

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Piyush Pansuriya8y ago
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
Manav Pandya8y ago
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