SQL statement help : select products tradable till specific time-CodePudding

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 before 18:00 CET that day. 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 --> Today until 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

CodePudding user response：

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

db<>fiddle

CodePudding user response：

What about :

SELECT *
FROM TableName
WHERE [Order Placement Date] < DATETIMEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),DAY(GETDATE()),18,0,0,0)
AND [Order Placement Date] >= DATETIMEFROMPARTS(YEAR(DATEADD(day, -1, GETDATE())),MONTH(DATEADD(day, -1, GETDATE())),DAY(DATEADD(day, -1, GETDATE())),18,0,0,0)