Date Load Issue

[byrnel0586]

I am having problems with loading a date (5/31/2017). ReportDate is the field which is shared between the two tables, but 2 date values keep showing for 5/31/2017. It is like QV is recognizing one of the dates as something other than a date.

I am using these expressions to create the date fields:

Table 1:

Date(ReportDate,'M/D/YYYY') as ReportDate

Table 2:

Date(MonthEnd(AddMonths(P_ReportDate,-11)),'M/D/YYYY') AS ReportDate

The field shows 2 dates for 5/31/2017...

How can I fix this?

[prieper]

without seeing the real data, you may ensure that both dates have the same data-type (in this case a date without decimals (= hours)).

Table 1:

Date(FLOOR(ReportDate),'M/D/YYYY') as ReportDate

Table 2:

Date(MonthEnd(AddMonths(FLOOR(P_ReportDate),-11)),'M/D/YYYY') AS ReportDate

may do the trick already.

Else you may have to check the import-formats and use DATE# in order to force the reading in a different format than your common format.

Peter

[byrnel0586]

Hi Peter,

Thank you, but when I add the FLOOR function the date disappears altogether...

[prieper · Accepted solution]

Seems that you are not loading a date.

Did you try to load the date-field with DATE#(), e.g.

DATE(DATE#(Field2Read, 'M/D/YYYY'), 'YYYYMMDD')

So the inner would ask QV to read the field in the given format M/D/YYYY (or whatever it is). This will result technically in a number. The outer format then will ask QV to show this number as a date in the format YYYYMMDD (or any other, as per your choice).

[byrnel0586]

Thank you. I ended up using a variation of the two and got it to work.