Wednesday, December 3, 2014

MSSQL Date Difference

DECLARE @DayIn datetime = '2014-12-03 10:05:00 PM'
DECLARE @DayOut datetime ='2014-12-04 07:00:00 AM'
DECLARE @BreakTimeIn  datetime = '2014-12-04 01:00:00 AM'
DECLARE @BreakTimeOut  datetime = '2014-12-04 02:00:00 AM'

SELECT GETDATE()
--SELECT @DayIn

--select cast(@DayIn as time) as DayIn

--select cast(@DayOut as time) as DayOut

--select cast(@BreakTimeIn as time) as BreakStartTime

--select cast(@BreakTimeOut as time) as BreakEndTime

-- Calculate the working hours

select cast((@DayOut - @DayIn)as time) as WorkingHours

-- Get breakhours

select cast(( @BreakTimeOut - @BreakTimeIn)as time) as Breaktime

-- Get totalworkingHours

select cast((@DayOut - @DayIn) - (@BreakTimeOut - @BreakTimeIn) as time) as TotalWorkingHours
--select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)

--SELECT
--CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond,
--CONVERT(VARCHAR(8),GETDATE(),101) AS DateOnly


--SELECT
--CONVERT(TIME,GETDATE()) AS HourMinuteSecond,
--CONVERT(DATE,GETDATE(),101) AS DateOnly
--GO

--SELECT
--LEFT(CONVERT(TIME(0),GETDATE()) ,5) AS [HourMinuteSecond]
--GO

No comments:

Post a Comment