Thanks, yes it works. This is much much easier. I have one more question.
We have 3 shift for the employees:
07:00 - 15:00
15:00 - 23:00
23:00 - 07:00
This is the calculated value I have used for StartTime.
CASE
WHEN [@field:Shift_ID] = 1 THEN
Dateadd(hour,7,CONVERT(DATETIME,CONVERT(DATE,GetUTCDate()-4, 103),103))
WHEN [@field:Shift_ID] = 2 THEN
Dateadd(hour,15,CONVERT(DATETIME,CONVERT(DATE,GetUTCDate()-4, 103),103))
WHEN [@field:Shift_ID] = 3 THEN
Dateadd(hour,23,CONVERT(DATETIME,CONVERT(DATE,GetUTCDate()-4, 103),103))
END
This for EndTime.
CASE
WHEN [@field:Shift_ID] = 1 THEN
Dateadd(hour,15,CONVERT(DATETIME,CONVERT(DATE,GetUTCDate()-4, 103),103))
WHEN [@field:Shift_ID] = 2 THEN
Dateadd(hour,23,CONVERT(DATETIME,CONVERT(DATE,GetUTCDate()-4, 103),103))
WHEN [@field:Shift_ID] = 3 THEN
Dateadd(hour,7,Dateadd(day,1,CONVERT(DATETIME,CONVERT(DATE,GetUTCDate()-4, 103),103)))
END
Can I use this (GetUTCDate()-4), because our local time is UTC−04:00.