topic Splitting Date String into Date and Time fields in Connectivity & Data Prep

Splitting Date String into Date and Time fields

Tue, 29 Sep 2015 18:51:11 GMT

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

mjm — Wed, 30 Sep 2015 10:32:42 GMT

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 — Wed, 30 Sep 2015 10:35:56 GMT

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