2 Replies Latest reply: Aug 20, 2017 7:46 PM by Jinghua Ji RSS

    having problem converting from SQL Date format

    Jinghua Ji

      Hi all,

       

      recently i have come across with an issue converting SQL date 'yyyy-mm-dd' into qlik 'dd/mm/yyyy'.

      first of all, i have to declare that it is not a wide spread issue, i have no problem converting dates and store them into QVD.

      it is particularly happening on one of the hundreds of QVDs i ever handled.

      the difference i can see from comparing with other working QVDs is that in this particular QVD generation process, i threw in a lot of temp table processing on SQL side. for example, i select data from source SQL table into a tenp table, then manipulate the data and select into another temp table, etc. at the last step, i grab all the data from the final temp table and start formatting the date fields.

      i tried, DATE(field), DATE(DATE#(field, 'yyyy-mm-dd')), DATE(DATE#(field,'yyyy-mm-dd'),'dd/mm/yyyy'), and many more, none of them worked, some even converted the dates wrong.

      however, if i leave as is, when i read in data from the QVD, and simply put a date() around the fields, it works.

      has anyone come across this issue before?