2 Replies Latest reply: Sep 30, 2015 6:35 AM by Sunny Talwar RSS

    Splitting Date String into Date and Time fields

      I have a date string in a CSV that looks like this:

       

      9/24/2015  9:01:00 AM

       

      Currently, I have this loading from my CSV file as "Send Date"

       

      LOAD

          Title,

          Subject,

          List,

          "Send Date",

          "Send Weekday",

          "Total Recipients",

          "Successful Deliveries",

          "Soft Bounces",

          "Hard Bounces",

          "Total Bounces",

          "Times Forwarded",

          "Forwarded Opens",

          "Unique Opens",

          "Open Rate",

          "Total Opens",

          "Unique Clicks",

          "Click Rate",

          "Total Clicks",

          Unsubscribes,

          "Abuse Complaints",

          "Times Liked on Facebook",

          "Folder Id",

          "Unique Id"

       

      FROM [lib://Newsletter Data/Sep_25_2015.csv]

      (txt, utf8, embedded labels, delimiter is ',', msq);

       

      I want to be able to format this string into both a time and date field such that I have a "Send Date" field with only the month, day, and year and a "Send Time" field with only the hour, minutes, seconds, and AM/PM.

       

      What changes do I need to make to my script in order to accomplish this?

        • Re: Splitting Date String into Date and Time fields
          Mark James

          Hello Brett,

           

          You can use functions like the month(), year(), day() weekday() functions to derive the different levels which you need.

           

          An example of how this can be done:

           

          LOAD

          "Send Date",

          Month("Send Date") as Month,

          Year("Send Date") as Year,

          Hour("Send Date") as Hour

           

          If needed you may need to format the date first using the date function and focusing on the second parameter.

          For example Date("Send Date", 'DD/MM/YYYY hh:mm:ss') as "Send Date"

           

          I hope this helps. Please can I ask you to mark this as helpful and answered if this does help.

          • Re: Splitting Date String into Date and Time fields
            Sunny Talwar

            You can try this also:

             

            Date(Floor(TimeStamp#('9/24/2015  9:01:00 AM', 'M/D/YYYY hh:mm:ss TT'))) as Date,

            Time(Frac(TimeStamp#('9/24/2015  9:01:00 AM', 'M/D/YYYY hh:mm:ss TT'))) as Time

             

            Replace '9/24/2015  9:01:00 AM' with your actual field to use in the load script.

             

            HTH

             

            Best,

            Sunny