Hi Expert,
I have to use date and time filter i query filters.
Example my cut of time is 15:00 si i need data from after 15:00 yesterday + before 15:00 today.
If i go with date filter only showing date not time.
Pls help me to filter with date and time.
I am using 4.3 .
BR
Sultan
First off, are you sure Insert Date contains the time? If it doesn’t, it is not going to matter what you filter is.
Assuming Insert Date does contain the time, there are a number of ways you can go about this.
1. Add universe objects with cutoff time
Given your screenshot, you must be using Object from this query for Today Minus 2 and Today. You could add objects to your universe that contain that 15:00 cut off time. The syntax for that would be specific to the database platform you are using. I will show that in the next option.
2. Create a free-hand SQL query with your key dates
This SQL code is for SQL Server. You may need to adjust it to work for your database.
DECLARE
@StartDate AS DATE
, @EndDate AS DATE
, @StartDateTime AS DATETIME
, @EndDateTime AS DATETIME;
SET @StartDate = DATEADD(DAY, -1, GETDATE())
SET @EndDate = GETDATE()
-- the space before the 15 is necessary
SET @StartDateTime = CONVERT (DATETIME, CONVERT (VARCHAR(12), CONVERT (DATE, @StartDate)) + ' 15:00:00');
SET @EndDateTime = DATETIMEFROMPARTS (YEAR (@EndDate), MONTH (@EndDate), DAY (@EndDate), 15, 0, 0, 0);
SELECT @StartDate AS [StartDate], @EndDate AS [EndDate], @StartDateTime AS [StartDateTime], @EndDateTime AS [EndDateTime]
I constructed the values for @StartDateTime and @EndDateTime differently purely to show it can be done at least two different ways. There are certainly more. The syntax used here would be the same when creating a universe object if you were to go that route.
I built free-hand SQL query with that code.
You must refresh (or run) that free-hand SQL date query in order to use those values in your primary query by choosing Result from another query (“Any” or “All”, doesn’t matter; either will work). Also, you cannot use the “Between” operator and choose values from another query. You have used “Greater than or Equal to” and “Less than or Equal to”.
There you have it.
You will likely need to create a custom format if you want to see those 24-hour time values. Here is mine…
Hope you are able to get what you need out of this.
Noel
