Say I have a table which stores order IDs and the corresponding products - like this:
Product ID | Order ID (PK) | Order Placement Date
Apple | 2455 | 2022-04-18 13:55:50.100
Apple | 2456 | 2022-04-18 15:50:40.100
Appel | 3457 | 2022-04-18 17:59:10.100
Appel | 3458 | 2022-04-18 18:40:10.100
Appel | 3459 | 2022-04-19 09:30:50.100
Appel | 3459 | 2022-04-19 14:20:20.100
How can I get on a daily basis all the products that are tradable ( Orders placed ) at any point from 18:00 CET yesterday and before 18:00 CET that day. So new products tradable after 18:00 CET on that day, placed for the first time, must be included in the next day select data ?
Also Order Placement Date would be a DateTime - UTC timezoned and I'm working with SQL Server 2019.
Something like
SELECT DISTINCT Product ID, Order ID, Order Placement Date
FROM TableName
Where Order Placement Date --> From yesterday 18h until today 18:00 CET
Select result sample when in 2022-04-18 :
Product ID | Order ID (PK) | Order Placement Date
Apple | 2455 | 2022-04-18 13:55:50.100
Apple | 2456 | 2022-04-18 15:50:40.100
Appel | 3457 | 2022-04-18 17:59:10.100
Select result sample when in 2022-04-19 :
Product ID | Order ID (PK) | Order Placement Date
Appel | 3458 | 2022-04-18 18:40:10.100
Appel | 3459 | 2022-04-19 09:30:50.100
Appel | 3459 | 2022-04-19 14:20:20.100
This is a little messy, but should work. I suspect there might be a less ugly method but oh well. I do, however, assume you are using a recent version of SQL Server, which supports AT TIME ZONE.
First we get the current date; I am assuming that "today" is from the server's perspective. Then we CONVERT to a date (to remove the time) and then back to a datetime2, so we can use AT TIME ZONE, which we state to be Central Europe. SQL Server which automatically process for DST and it'll assume the datetime2 value was correct for the timezone. Then we change that time to UTC and CONVERT back to a datetime so that there is no implicit conversions on the column. Then we do the same for the upper boundary, but with 8 hours added on:
CREATE TABLE dbo.YourTable (ProductID int,
OrderID int,
OrderPlacementDate datetime); --Per question, this is UTC
INSERT INTO dbo.YourTable (ProductID,
OrderID,
OrderPlacementDate)
VALUES(2,2,'20220418 21:48:59.123'), --Would be 2022-04-18 23:48:59.123 CEST
(1,1,'20220418 22:15:17.167'), --Would be 2022-04-19 00:15:17.167 CEST
(1,1,'20220419 15:15:17.167'), --Would be 2022-04-19 17:15:17.167 CEST
(1,1,'20220419 16:15:17.167'); --Would be 2022-04-19 18:15:17.167 CEST
GO
SELECT *
FROM dbo.YourTable
WHERE OrderPlacementDate >= CONVERT(datetime,(CONVERT(datetime2(3),CONVERT(date,GETDATE())) AT TIME ZONE 'Central Europe Standard Time') AT TIME ZONE 'UTC')
AND OrderPlacementDate <= CONVERT(datetime,DATEADD(HOUR,18,CONVERT(datetime2(3),CONVERT(date,GETDATE())) AT TIME ZONE 'Central Europe Standard Time' AT TIME ZONE 'UTC'))
GO
DROP TABLE dbo.YourTable