Problems while converting date format

[#1]

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

[shree909]

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

[rwunderlich · Accepted solution]

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

[#4]

Works perfectly! Thank you so much!