3 Replies Latest reply: Apr 6, 2014 3:53 PM by Massimo Grossi RSS

    Format a text field to a YYYY/MM/DD date field

    david balmer

      Hi:

       

      I have a tiny sample here in which I am ultimately trying to extract the YEAR, MONTH, DAY  out of a comma-delimited source.  The source has the following three fields:

      DogName

      OwnerName

      Cost

      PurchaseDate

       

      The PurchaseDate field in each record starts with a + sign and looks like; +19950610

       

      DogName,OwnerName,Cost,PurchaseDate

      Clancy,David,500.00,+19950610

      Casey,Alec,650.00,+20010331

      Fraggle,Nadine,700.00,+20040201

       

      I can remove the + sign with the following script but cannot get any further in rendering the field as a Date and then in the attempt to extract the Year, Month and Day as mentioned.   There is so much information in the Community on how to do it but I have had no success with any.  Though my sample is tiny, it represents an issue with a much larger file in the hundreds of thousands of records.  Any help in constructing the corrrect script would be most appreciated.

       

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='$#,##0.00;($#,##0.00)';

      SET TimeFormat='h:mm:ss TT';

      SET DateFormat='YYYY/MM/DD';

      SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

       

      [Dogs]:

      LOAD DogName,

                OwnerName,

                Cost,    

               //strip out the leading + sign fromn the PurchaseDateField

          

           KeepChar(PurchaseDate,'0123456789')as DateTrim

          

      FROM

      [E:\RMASourceData\DogRecords.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

      Thank you very much