Search code examples
sqlsql-serverdatetimedatepart

MS SQL query on Punch time

I am trying to retrieve information from 2 database tables (MS SQL DB) on the basis of shift start time (date time format). I want the records with a date range and a specific time of those days, but it seems the query is not working The query which I wrote is:

select p.fullnm 'Fullname',
      t.startdtm 'Start Date/Time'
FROM TIMESHEET t
      join PERSON p on p.personid = t.employeeid
WHERE t.startdtm between '2016-01-01' and '2016-12-05'
AND datepart(hh, startdtm) between 6 and 8
AND datepart(mi, startdtm) between 00 and 15

What I want is: a list of all employees who worked between 6 am and 7:15 am from 1-Jan till 5-Dec. Any help/suggestion will be appreciated.

Solution

  • Something like this?

    WHERE t.startdtm between '2016-01-01' and '2016-12-05'
  AND cast(t.startdtm as time) between '06:00' and '07:15'