topic Re: Date's Reformatted Following a Where Clause in App Development

Date's Reformatted Following a Where Clause

m_perreault — Wed, 09 May 2018 14:42:38 GMT

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

Re: Date's Reformatted Following a Where Clause

YoussefBelloum — Wed, 09 May 2018 14:58:47 GMT

Hi,

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

Re: Date's Reformatted Following a Where Clause

m_perreault — Wed, 09 May 2018 15:03:38 GMT

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

Re: Date's Reformatted Following a Where Clause

YoussefBelloum — Wed, 09 May 2018 15:06:44 GMT

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 ?

Re: Date's Reformatted Following a Where Clause

m_perreault — Wed, 09 May 2018 16:06:03 GMT

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