topic Re: Splitting Date String into Time and Date Fields in Connectivity & Data Prep

Splitting Date String into Time and Date Fields

Tue, 29 Sep 2015 19:35:09 GMT

I have a date string in a CSV field that I currently have labeled as "Send Date" and looks like this:

9/24/2015 9:01:00 AM

I want to be able to split this date string into two fields:

9/24/2015 as "Date Sent"

9:01:00 AM as "Time Sent"

Currently my data load script looks like this:

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);

What do I need to add in order to get the date/time split I want from my source data?

Re: Splitting Date String into Time and Date Fields

Anonymous — Wed, 30 Sep 2015 08:07:02 GMT

Hello,

Try subfield() function.

Something like

subfield([Your Field],' ',1) as Date,

subfield([Your Field],' ',2) as Time,

Serhan

Re: Splitting Date String into Time and Date Fields

jonathandienst — Wed, 30 Sep 2015 08:26:33 GMT

Date(Floor([Send Date])) As [Send Date],

Time(Frac([Send Date])) As [Send Time],

Re: Splitting Date String into Time and Date Fields

jonathandienst — Wed, 30 Sep 2015 08:31:22 GMT

This assumes that the [Send Date] field is being recognised as a date time (numeric) and not as a string. If [Send Dat] is a string (left aligns in a list box), then you need:

Date(Floor(Timestamp#([Send Date], 'M-D-YYYY h:mm:ss TT'))) As [Send Date],

Time(Frac(Timestamp#([Send Date], 'M-D-YYYY h:mm:ss TT'))) As [Send Time],

Tweak the format string in bold so the date and time are being correctly populated.

Re: Splitting Date String into Time and Date Fields

varaprasad0816 — Thu, 27 Feb 2020 14:15:36 GMT

Correct Answer.

I have executed