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

    Problems while converting date format

      Hi,

       

      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.

       

       

      Thanks,

      Dave

        • Re: Problems while converting date format
          sree anaarasi

          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

           

          or

           

          =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.

             

            Tickets:

            LOAD

                      *,

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

                        Month(Date) as DateMonth,

                       Year(Date) as DateYear

            ;

            LOAD

                      'Tickets' as Source,

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

                      [Interaction Created Timestamp] as Dateformat,

                      'ITSM Created' as DateFlag

            FROM

            source.xlsx

            (ooxml, embedded labels, table is Report);

             

            -Rob