'SQL : 12 hr shift function from 7am - 7am
I need to query all production records by 12 hr. shift. 7am-7am. if the date is after midnight and before 7am it's actually the previous day shift. In the below example I need to make them all 2022-01-01 like the last column. If I query by 2022-01-01 I don't get all the rows. Can I use a function for this to compare the time and make it the previous day?
declare @temp table
(
Emp_id int,
Time datetime,
shiftDate date
);
insert into @temp values (1, '2022-01-01 08:10:00:000', '2022-01-01')
insert into @temp values (1, '2022-01-01 10:21:00:000', '2022-01-01')
insert into @temp values (1, '2022-01-01 13:10:00:000', '2022-01-01')
insert into @temp values (1, '2022-01-01 22:22:00:000', '2022-01-01')
insert into @temp values (1, '2022-01-02 02:15:00:000', '2022-01-01')
insert into @temp values (1, '2022-01-02 04:22:00:000', '2022-01-01')
insert into @temp values (1, '2022-01-02 06:18:00:000', '2022-01-01')
insert into @temp values (1, '2022-01-02 06:55:00:000', '2022-01-01')
select * from @temp
select * from @temp
where convert(date, [time]) = '2022-01-01'
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
