Skip to main content
Announcements
See why Qlik is a Leader in the 2024 Gartner® Magic Quadrant™ for Analytics & BI Platforms. Download Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting Date String into Time and Date Fields

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?

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

Hello,

Try subfield() function.

Something like

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

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

BR

Serhan

View solution in original post

4 Replies
Anonymous
Not applicable
Author

Hello,

Try subfield() function.

Something like

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

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

BR

Serhan

jonathandienst
Partner - Champion III
Partner - Champion III

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
varaprasad0816
Creator II
Creator II

Correct Answer.

I have executed