Convert DateTimeOffset to DateTime in a where between 23:00 previous day an 07:00 Am next day for past 2 weeks

Question

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

Which DBMS are you using? Microsoft SQL Server? Please add the appropriate tag for your DBMS. — Mark Rotteveel
– Mark Rotteveel, Commented Dec 13, 2022 at 12:51
SWITCHOFFSET(CreatedOn,'+00:01') seems wrong; that would be a 1 minute offset from UTC. Is that what you actually want? No one actually observes that. — Thom A
– Thom A ♦, Commented Dec 13, 2022 at 13:47
Jeeze, how did I not see that. Thanks for your observation. But unfortunately that was not the bug........but another bug ;-) Will fix it in my original post — user1702369
– user1702369, Commented Dec 13, 2022 at 14:51

Gaël James · Accepted Answer · 2022-12-13 13:28:17Z

1

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

edited Dec 13, 2022 at 13:28

answered Dec 13, 2022 at 13:23

Gaël James

3051 silver badge14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1702369 Over a year ago

Actually, rookie mistake I'va made. It makes sense to check if the hour part is either above 23 or below 7, the date is ordered...so it works :-) Your solution helped me except the 3rd parameter of the CONVERT I changed from '+00:01' to 103. It expects an int and not a varchar.

Collectives™ on Stack Overflow

Convert DateTimeOffset to DateTime in a where between 23:00 previous day an 07:00 Am next day for past 2 weeks

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related