I would like to automate a query without anyone changing the date manually. I have tried "GETDATE()" to no avail. I plan to use Google Sheets script editor to automate this task. Here's a portion of the code where we change the date manually:
Left Join
OrderType ON SalesOrder.OrderTypeID = OrderType.OrderTypeID
Left Join
Location On EventRef.LocationID = Location.LocationID
Inner Join
Client On Location.ClientID = Client.ClientID
Inner Join
RequestSource On SalesOrder.RequestSourceID = RequestSource.RequestSourceID
WHERE
EventRef.EventDateTime > '11-Oct-21 0:00:00 AM'
AND EventRef.EventDateTime < '11-Oct-21 23:59:59 PM'
AND OrderStatus.OrderStatusName <> 'Added in error'
AND OrderStatus.OrderStatusName <> 'Cancelled;
CodePudding user response:
GETDATE
/SYSDATETIME
or GETUTCDATE
/SYSUTCDATETIME
or even SYSDATEIMEOFFSET
(which depends on your data) is what you want and some very simply date logic:
...
WHERE EventRef.EventDateTime >= CONVERT(date, GETDATE())
AND EventRef.EventDateTime < CONVERT(date, DATEADD(DAY, 1, GETDATE()))
...