David Lai's Business Intelligence Blog

In Web Intelligence there are a variety of helpful date functions that you can use.

2 in particular are CurrentDate() and CurrentTime().

You would think that CurrentDate() returns just the current date and CurrentTime() returns the current datetime.

However both return the exact same result which makes it confusing, especially if you are using date dimensions where the time is stripped off. The funny thing is that most people don’t even catch this issue when they are using CurrentDate() !

Let’s take the example below assuming today is April 10, 2017:

Create a table and insert the formula CurrentDate() into one of the cells.

Looks simple enough right?

Well then try this! Add 2 compare formulas. One that compares CurrentDate() with today no time and another that checks if CurrentDate() is <= today no time.

You’ll see that the CurrentDate() formula does not equal today! Which is troublesome because date dimensions generally contain just the date itself and not the time.

If you change the format of the cell to long date, you will see that you are getting the current time.

Unfortunately this is not user friendly as both CurrentDate() and CurrentTime() are now redundant formulas. When I select CurrentDate() I am expecting just the date itself and not the time as well!  Anyhow below contains a quick workaround to fix the issue.

Step 1

Create a new variable called c_current_date, that will strip the time from CurrentDate().

Step 2

Now compare the variable c_current_date variable with a today no time.

We now see the correct answer!