Convert DateTimeOffset to DateTime in a where between 23:00 previous day an 07:00 Am next day for pa-CodePudding

I have to make an query on a table where the datetime field is of type datetimeoffset. The query itself is already difficult, but now working with conversions makes it even more complicated. What I want out of the table is to retrieve everything between 23:00 previous day to 07:00 the next morning for the past 2 weeks. I have tried numerous ways, but think I am just staring at a screen momentarily. Below is one of my last attempts before posting here, what am I missing here:

SELECT * 
FROM Occurrences 
 WHERE SWITCHOFFSET(CreatedOn,' 01:00') >= DATEADD(hh,23,DATEADD(day, DATEDIFF(day, 0, 
  SWITCHOFFSET(CreatedOn,' 01:00') - 1), 0))
 AND SWITCHOFFSET(CreatedOn,' 01:00') <    DATEADD(hh,7,DATEADD(day, DATEDIFF(day, 0, 
  SWITCHOFFSET(CreatedOn,' 01:00')), 0))
 ORDER BY CreatedOn DESC

Just note This is without the 2 weeks filter. Just wanted to see some results atleast, but get an error:

Operand type clash: datetimeoffset is incompatible with int

The CreatedOn has a entry like "2022-11-15 09:24:13.6096718 01:00" and the type of field is a datetimeoffset(7)

This does work though in it simplest form:

  SELECT TOP 1 SWITCHOFFSET(CreatedOn,' 01:00') FROM Occurrences ORDER BY CreatedOn DESC

CodePudding user response：

Just check that the hour part is either above 23 or below 7, then check it occurred less than 2 weeks ago.

SELECT *
FROM Occurrences
WHERE (DATEPART(hh, SWITCHOFFSET(CreatedOn,' 00:01')) >= 23 OR DATEPART(hh, SWITCHOFFSET(CreatedOn,' 00:01')) < 7) AND (SWITCHOFFSET(CreatedOn,' 00:01') > CONVERT(datetimeoffset, DATEADD(day, -14, GETDATE()),' 00:01'))
ORDER BY CreatedOn DESC