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

Brendan Broughton
Brendan Broughton Active Partner
Third Anniversary 100 Comments 100 Up Votes 25 Likes
in Platform

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.

image

 

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

   image

 

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?

Tagged:

Answers

  • Helmut Heimann
    Helmut Heimann Employee, Community Captain
    50 Answers 500 Comments 250 Likes Fourth Anniversary
    edited March 2020

    Hi Brendan Broughton,

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

    image

    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

  • Brendan Broughton
    Brendan Broughton Active Partner
    Third Anniversary 100 Comments 100 Up Votes 25 Likes
    edited March 2020

    Thanks Helmut Heimann, that setting makes no difference, I still get the incorrect dates showing in the BROWSE.

     

    The issue is that BOARD is reading the dates in correctly, so I'm not sure changing anything will fix it, it may make what is currently correct incorrect. Financial and Production data is read by day and reconciles back to what is expect, so when stored to cube it is correct. The Date (Comm, Term, Birth) that is read/stored in the cube IS correct.

     

    What is not correct is the SQL Browse.  I'm at a loss as to why BOARD shows one thing and then stores another?!

     

    I change the data type to below.

    image

    and still get the same 'previous day' dates showing in browse.

    image

Categories