Today() variable and time stamp issue

[ndeeleysww]

Hi there,

I'm pulling date from Access to create a bar chart.

I have created a variable in my load script which pulls back today:

LET vTodayTY = Today();

...so I can pull out all completed records from the start of the year to this date in the measure:

sum({$<ChargeableYN={'1'},CompletionDate={">=$(#vFirstDayThisFinancialYear)<=$(#vTodayTY)"}>} [AmountCharged])

My Completion Date is pulled out of the Access DB like this:

date(CompletionDate ,'DD/MM/YYYY') as CompletionDate

I have one record with a CompletionDate of 08/01/2015 10:35:42. This record is not included in my bar chart using the start of the year and the Today() variable. If I change the CompletionDate to the 07/01/2015 it is included.

Is there a timestamp I'm not removing that is causing this record not to be used as part of the calculation?

Cheers

Neil

[#2]

Hi,

Try using the Now() function instead of the Today() function:

LET vTodayTY = Now();

[jonathandienst]

Hi

Today() returns a time of 00:00:00 for today's date, so any transaction happening today will have a timestamp > today(). You can either remove the time component from the CompletionDate during load, or else change your definition of vTodayTY to include a time component:

LET vTodayTY = TimeStamp(Today() + (1 - 1/86400));  // today at 23:59:59

HTH

Jonathan

[danielact · Accepted solution]

You can either use Now() instead of today(), or you can use DayStart on your data field like this:

date(DayStart(CompletionDate) ,'DD/MM/YYYY') as CompletionDate

That will change all the timestamps to 00:00:00 so they'll be the same as the Today() function.

[ndeeleysww]

Brilliant - thank you Jose, Jon and Dan for your replies. I've gone with Dan's as it removes all the ambiguity when I load in the data from the date fields.

Cheers

Neil