Dates shown in Browse and used in WHERE are offset by 1

Brendan Broughton

Just wondering if anyone in the community has come across this issue before and found a robust/correct way of dealing with it.  BROWSE doesn't match what is loaded into a CUBE.

 

The Browse on a SQL query shown below lists several dates by employee.

 

The Dataview below shows what is actually loaded into the relevant date cubes, with the Commencement date also loaded into a text cube for comparison

   

 

The dates don't match!

What is showing in the BROWSE (and also used in the SQL where statement) are offset by 1 day. So someone that has a Commencement on 1 July will actually be mapped against June, because it sees their commencement date as 30/6.

 

Also, today is 31/7/18, yet when I use sysdate() or curdate() (Progress DB) they both return 30/7/18.

 

I can quite easily reverse the -1 day offset in the WHERE statement by adding a day, but I would rather work out why the SQL is seeing/using a different date to what is actually there, in case it's something temporary and a fix like that will cause issue down the track.

 

Does anyone have any ideas what would be causing this?

Helmut Heimann

Hi Brendan Broughton,

the default setting in your SQL options is "convert Date as Number"

Have you tried to change this to the format your SQL database uses (or shows as D/MM/YYYY)? My assumption would be that dates are calculated differently in your SQL and BOARD, i.e. the number representing the date is different.

 

Kind Regards,

Helmut