Truncate Enddate to Midnight

Transact-SQL
1

Problem:
I want date where date = TIME_START but if TIME_END of event >= Midnight THEN TIME_END of event = Midnight Please I need a SQL code that represents the above problem statement.

2

@Hamdaweh,
Please post DDL for the table(s), inserts with sample data and complete SQL of your query

3

Sample%20Data
Sample Data.png1718×462 32.3 KB

SELECT TOP (100) PERCENT Date, TimeStart, TimeEnd, Shift, MACHINE_ID AS EquipmentID, EquipmentType, SUM(DelayTime + ReadyTime + ScheduledDownTime + UnscheduledDownTime + ShiftChangeTime + SpareTime)
AS TotalTime, SUM(DelayTime) AS DelayTime, SUM(ReadyTime) AS ReadyTime, SUM(ScheduledDownTime) AS ScheduledDownTime, SUM(UnscheduledDownTime) AS UnscheduledDownTime, SUM(ShiftChangeTime)
AS ShiftChangeTime, SUM(SpareTime) AS SpareTime, DelayCategory, WORK_REASON
FROM (SELECT TOP (100) PERCENT CASE WHEN DATEADD(hh, - 7, dbo.v_dr_work_code.TIME_START) IS NULL THEN '2012-01-01' ELSE CONVERT(DATE, CASE WHEN CONVERT(VARCHAR(8), DATEADD(hh, - 7,
dbo.v_dr_work_code.TIME_START), 108) BETWEEN '0' AND '86400' THEN CONVERT(date, DATEADD(hh, - 7, dbo.v_dr_work_code.TIME_START)) ELSE CONVERT(date, DATEADD(d, - 1, DATEADD(hh, - 7,
dbo.v_dr_work_code.TIME_START))) END) END AS Date, CASE WHEN CONVERT(VARCHAR(8), DATEADD(hh, - 7, dbo.v_dr_work_code.TIME_START), 108) BETWEEN '07:00' AND
'19:00' THEN 'Day Shift' ELSE 'Night Shift' END AS Shift, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), dbo.v_dr_work_code.TIME_START) AS TimeStart, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(),
GETDATE()), dbo.v_dr_work_code.TIME_END) AS TimeEnd, DATEPART(YYYY, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), dbo.v_dr_work_code.TIME_START)) AS Year, DATEADD(hh, - 7,
dbo.v_dr_work_code.TIME_START) AS CheckDate, dbo.v_dr_work_code.MACHINE_ID, dbo.v_dr_work_code.WORK_REASON, dbo.dr_c_work_class.name AS DelayCategory, DATEDIFF(ss,
dbo.v_dr_work_code.TIME_START, dbo.v_dr_work_code.TIME_END) AS Duration, dbo.dr_c_machine.model_name AS EquipmentType, CASE WHEN dr_c_work_class.name = 'Delay' AND
v_dr_work_code.WORK_REASON != 'SHIFT CHANGE' THEN DATEDIFF(ss, dbo.v_dr_work_code.TIME_START, dbo.v_dr_work_code.TIME_END) ELSE 0 END AS DelayTime,
CASE WHEN dr_c_work_class.name = 'Production' THEN DATEDIFF(ss, dbo.v_dr_work_code.TIME_START, dbo.v_dr_work_code.TIME_END) ELSE 0 END AS ReadyTime,
CASE WHEN dr_c_work_class.name = 'Down' AND v_dr_work_code.WORK_REASON = 'M_PM' THEN DATEDIFF(ss, dbo.v_dr_work_code.TIME_START, dbo.v_dr_work_code.TIME_END)
ELSE 0 END AS ScheduledDownTime, CASE WHEN dr_c_work_class.name = 'Down' AND v_dr_work_code.WORK_REASON != 'M_PM' THEN DATEDIFF(ss, dbo.v_dr_work_code.TIME_START,
dbo.v_dr_work_code.TIME_END) ELSE 0 END AS UnscheduledDownTime, CASE WHEN dr_c_work_class.name = 'Spare' THEN DATEDIFF(ss, dbo.v_dr_work_code.TIME_START, dbo.v_dr_work_code.TIME_END)
ELSE 0 END AS SpareTime, CASE WHEN dr_c_work_class.name = 'Delay' AND v_dr_work_code.WORK_REASON = 'SHIFT CHANGE' THEN DATEDIFF(ss, dbo.v_dr_work_code.TIME_START,
dbo.v_dr_work_code.TIME_END) ELSE 0 END AS ShiftChangeTime, dbo.v_dr_work_code.TIME_START
FROM dbo.dr_c_machine LEFT OUTER JOIN
dbo.v_dr_work_code ON dbo.dr_c_machine.machine_id = dbo.v_dr_work_code.MACHINE_ID RIGHT OUTER JOIN
dbo.dr_c_work_class ON dbo.v_dr_work_code.WORK_CLASS_ID = dbo.dr_c_work_class.work_class_id RIGHT OUTER JOIN
dbo.dr_c_work_reason ON dbo.dr_c_work_class.work_class_id = dbo.dr_c_work_reason.work_class_id
GROUP BY DATEPART(YYYY, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), dbo.v_dr_work_code.TIME_START)), dbo.v_dr_work_code.MACHINE_ID, dbo.v_dr_work_code.WORK_REASON, DATEADD(mi,
DATEDIFF(mi, GETUTCDATE(), GETDATE()), dbo.v_dr_work_code.TIME_START), DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), dbo.v_dr_work_code.TIME_END), dbo.dr_c_work_class.name, DATEADD(hh,
- 7, dbo.v_dr_work_code.TIME_START), dbo.dr_c_machine.model_name, DATEDIFF(ss, dbo.v_dr_work_code.TIME_START, dbo.v_dr_work_code.TIME_END), dbo.v_dr_work_code.TIME_START
HAVING (DATEPART(YYYY, DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), dbo.v_dr_work_code.TIME_START)) = 2018)
ORDER BY TimeStart, TimeEnd) AS DrillDelay
GROUP BY MACHINE_ID, Shift, EquipmentType, DelayCategory, WORK_REASON, Date, TimeStart, TimeEnd
HAVING (Date BETWEEN @startDate AND @stopDate) AND (MACHINE_ID = 4085)
ORDER BY TimeStart, Shift

4

Sample%20Data2
Sample Data2.png1718×462 78 KB

Thank you

5

The reason we ask for DDL and inserts is we can't test our solution without keying in your data. Without the benefit of testing on your sample data:

DECLARE @t table(dt datetime);
INSERT @t (dt) VALUES('20180329 14:36:23.403');
SELECT
     dt
   , DateAdd(dd, DateDiff(dd, 0, dt), 1) Midnight
FROM @t;

image

6

Hi Joseph,

I am working in SQL Server. Please how do I get the DDLs?

Thank you

Hamdaweh

7

So I want my TIME_END column to be converted to midnight when the event spills over into the next day.

8 
DECLARE @t table(dt datetime);
INSERT @t (dt) VALUES('20180329 14:36:23.403');
SELECT
     dt
   , DateAdd(dd, DateDiff(dd, 0, dt), 0) Midnight
FROM @t;

image

9 
select
    case
        when DateDiff(Day, StartDate, EndDate) = 0  -- Same Day
        then EndDate
        else DateAdd(day, DateDiff(day, 0, EndDate), EndDate
        end

This will round the time down to midnight. I'm not sure if that's what you want.