Splitting Date String into Time and Date Fields

[#1]

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?

[Anonymous · Accepted solution]

Hello,

Try subfield() function.

Something like

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

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

BR

Serhan

[jonathandienst]

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

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

[jonathandienst]

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.

[varaprasad0816]

Correct Answer.

I have executed