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.

Manav Pandya · Answer

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

Piyush Pansuriya · Answer

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 BEGIN INSERT INTO @TimeTable( DAY  START_TIME END_TIME ) SELECT DATENAME(WEEKDAYDATEADD( 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