Date's Reformatted Following a Where Clause

[m_perreault]

Hi All,

I am loading data from a QVD and when I load it as is my dates are formatted 'MM/DD/YYYY' on the Front End.  However when I use a where clause in the load such as Where OrderDate = '04/30/2018'.  My dates become formatted as numbers in the front end so '04/30/2018' becomes 43220.  Does anyone know why adding a where clause would change the formatting of the date?

Thanks,
Mark

[YoussefBelloum]

Hi,

can you attach the LOAD script (image or text) on which you're making the WHERE ?

[m_perreault]

My load script is just

TestTable:

Load *

From [($(vQVDPath)Test.qvd)] (qvd)

Where OrderDate = '04/30/2018';

I have also tried using the following

Where Date(OrderDate,'MM/DD/YYYY') = '04/30/2018';

Where Date(Floor(OrderDate),'MM/DD/YYYY') = '04/30/2018';

And am still seeing the date be changed to a num whenever I use the Where Clause formatted as a Date when I do not.

Thanks,

Mark

[YoussefBelloum]

did you try this:

TestTable:

Load

Date(OrderDate ,'MM/DD/YYYY') as OrderDate,

.

.

.

From [($(vQVDPath)Test.qvd)] (qvd)

Where Date(OrderDate,'MM/DD/YYYY') = '04/30/2018';

also, what is the format of this OrderDate field ? and what is your Date system variable format ?

[m_perreault]

My Date System Variable Format is the Default 'M/D/YYYY'.

I have tried using

TestTable:

Load

Date(OrderDate ,'MM/DD/YYYY') as OrderDate,

.

.

.

From [($(vQVDPath)Test.qvd)] (qvd)

Where Date(OrderDate,'MM/DD/YYYY') = '04/30/2018';

Which will yield the desired formatting in the Front End.  I was more just curious as to why the addition of the Where Clause would change the formatting from Date to Num.  I believe the format of the OrderDate field is a timestamp which has been formatted Date(OrderDate,'MM/DD/YYYY') but has not had Date(Floor(OrderDate,'MM/DD/YYYY') which I am wondering if this is having an effect.

Thanks,

Mark