Microstrategy filter data by today's date

Question

I am trying to filter data from Microsoft SQL in an intelligent cube by today's date.
The format of the date from the database is YYYY-MM-DD HH:MM:SS.
I want to include all of today's date regardless of the time. This is the filter I have now

(tblJournal.DateCreated > AddDays(CurrentDate(),-1))


AND

(tblJournal.DateCreated < AddDays(CurrentDate(),1))

The problem with this filter is it must include time because if for example I run this filter on the 17th at noon it would include all entries on the 16th past noon until the 18th before noon. How can I change this function to only return results from today's date?

Answer 1

In MicroStrategy I suggest you to create an additional form "Date" for the attribute you mapped on tblJournal.DateCreated and define it as Date(tblJournal.DateCreated), where Date is a MicroStrategy function, and the format is of course date.

Then you can define the filter for your cube as DateCreated attribute, Qualify on the form Date, equal to Today (using the dynamic date functionality, the Calendar icon beside the value box).

Otherwise on the SQLServer world you can do this:

DATEADD(dd, 0, DATEDIFF(dd, 0, tblJournal.DateCreated)) = DATEADD(dd, 0, DATEDIFF(dd, 0, CurrentDate()))

as suggested here

Answer 2

you can get the current date without time like SELECT CONVERT(datetime, CONVERT(varchar, GETDATE(), 101))

AND

How can I change this function to only return results from today's date?

i think you can do this like

(tblJournal.DateCreated > AddDays(CurrentDate(),0))

i didn't test the code, hope this will help you... happy coding ;)