Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
can you attach the LOAD script (image or text) on which you're making the WHERE ?
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
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 ?
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