3 Replies Latest reply: Jul 29, 2013 5:51 PM by Dave Schadock RSS

    Problems while converting date format



      could somebody pls take a look into the following qvw and Excel file and tell me, why QV does not interpret the date format in the right way?!

      I tried so many possibilities in the script - nothing worked...


      background: I need to convert the field 'Dateformat' into the format of DateMonth and DateYear (MM/YYYY) for my mastercalendar.





        • Re: Problems while converting date format
          Sree Anarasi

          Hi use this expression in the script to calculate the dates


          =Date(Date#(SubField(Date,' ',-2),'MM/DD/YYYY'),'DD/MM')   AS DateMonth for date and month


          =Date(Date#(SubField(Date,' ',-2),'MM/DD/YYYY'),'DD/YYYY')  As DateYear




          =Date(Date#(SubField(Date,' ',1),'MM/DD/YYYY'),'DD/MM')   AS DateMonth for date and month


          =Date(Date#(SubField(Date,' ',1),'MM/DD/YYYY'),'DD/YYYY') 



          replace date with [Interaction Created Timestamp]  field..

          • Re: Problems while converting date format
            Rob Wunderlich

            I would approach it like this. [The Interaction Created Timestamp] is being correctly interpreted. You'll need to floor() that field to get just the date portion. Then you can use a preceding load to create additional fields from the Date.





                      Date(MonthStart(Date),'MMM-YYYY') as MonthYear,

                        Month(Date) as DateMonth,

                       Year(Date) as DateYear



                      'Tickets' as Source,

                      Date(Floor([Interaction Created Timestamp])) as Date,

                      [Interaction Created Timestamp] as Dateformat,

                      'ITSM Created' as DateFlag



            (ooxml, embedded labels, table is Report);