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

Options
Brendan Broughton
Brendan Broughton Active Partner
Level 200: Leveraging Board for Business Insights Level 100: Foundations of Building in Board First Anniversary First Comment
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
    March Badge of the Month February Badge of the Month Community Captain April Badge of the Month
    edited March 2020
    Options

    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
    Level 200: Leveraging Board for Business Insights Level 100: Foundations of Building in Board First Anniversary First Comment
    edited March 2020
    Options

    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