sql server - SQL: How to get the actual shift values -

i´ve created report in ssrs in display machines volume last hour:

where  intervalstartdatetime >= dateadd(hour, -2, getdate())     , intervalstartdatetime <= dateadd(hour, -1, getdate()) 

from last 4 hours:

where  intervalstartdatetime >= dateadd(hour, -4, getdate())     , intervalstartdatetime <= dateadd(hour, -3, getdate()) 

and last 8 hours:

where  intervalstartdatetime >= dateadd(hour, -8, getdate())     , intervalstartdatetime <= dateadd(hour, -7, getdate()) 

and additionaly display row actual shift --> here have problems. there 3 shifts. night shift: from: 05:45 - 13:45 shift: from: 13:45 - 21:45 late shift: : 21:45 - 05:45

table:

intervalstartdatetime 2017-03-28 15:30:00.000 2017-03-28 15:30:00.000 2017-03-28 15:30:00.000 2017-03-28 15:30:00.000 2017-03-28 15:30:00.000 

how can actual shift value? using microsoft sql server management studio

please try -

where ( intervalstarttime >= case                                  when convert( time, getdate() ) < '05:45:00' convert( date, dateadd( day, -1, getdate() ) ) + cast( '21:45:00' datetime )                                  when convert( time, getdate() ) < '13:45:00' convert( date, getdate() ) + cast( '05:45:00' datetime )                                  when convert( time, getdate() ) < '21:45:00' convert( date, getdate() ) + cast( '13:45:00' datetime )                                  else                                              convert( date, getdate() ) + cast( '21:45:00' datetime )                              end ) 

note : first time trying sql-server, i'm going have start sometime guess. :)

if have questions or comments, please feel free post comment accordingly.

further reading...

combining date , time fields datetime, sql server 2008

how return date part sql server datetime datatype