Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
manojkulkarni
Partner - Specialist II
Partner - Specialist II

Convert String to Date & Time

Hi Friends,

How to convert the below string into Date&Time Format ?  If data coming in different formats then how it should be handled ?

"Thu  3/9/2017 6:10 PM"

1 Solution

Accepted Solutions
sunny_talwar

Like this

Table:

LOAD Date as Old_Date,

  TimeStamp(Alt(Num(Date#(Date, 'M/D/YYYY')), Num(TimeStamp#(Trim(Mid(Date, FindOneOf(Date, '1234567890'))), 'M/D/YYYY h:mm TT')))) as New_Date;

LOAD * Inline [

Date

"Thu  3/9/2017 6:10 PM"

2/22/2017

];

View solution in original post

12 Replies
satheshreddy
Creator III
Creator III

‌Hi Manoj,

use

date(date#(),required date format)

REgards

SAthish

sunny_talwar

May be something like this:

=TimeStamp(TimeStamp#(Trim(Mid('Thu  3/9/2017 6:10 PM', FindOneOf('Thu  3/9/2017 6:10 PM', '1234567890'))), 'M/D/YYYY h:mm TT'))

PrashantSangle

Hi,

try date(date#(dateField,'www  D/M/YYYY hh:mm TT'))

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
PrashantSangle

If there are more than 1 format then use alt()

see alt() in help menu for details.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
manojkulkarni
Partner - Specialist II
Partner - Specialist II
Author

Not Working Prashant

sunny_talwar

I did try this dreamer4‌, but for some reason, it did not work for me. Have you tested this out and its working for you? Can you share a sample where it is working?

Thanks,

Sunny

sunny_talwar

Did you try this?

=TimeStamp(TimeStamp#(Trim(Mid('Thu  3/9/2017 6:10 PM', FindOneOf('Thu  3/9/2017 6:10 PM', '1234567890'))), 'M/D/YYYY h:mm TT'))

or this for your date field

=TimeStamp(TimeStamp#(Trim(Mid(DateFieldName, FindOneOf(DateFieldName, '1234567890'))), 'M/D/YYYY h:mm TT'))

manojkulkarni
Partner - Specialist II
Partner - Specialist II
Author

Thanks Sunny. It worked

How to handle the below scenarios, both are coming in same column with different formats.

"Thu  3/9/2017 6:10 PM"

2/22/2017

sunny_talwar

Like this

Table:

LOAD Date as Old_Date,

  TimeStamp(Alt(Num(Date#(Date, 'M/D/YYYY')), Num(TimeStamp#(Trim(Mid(Date, FindOneOf(Date, '1234567890'))), 'M/D/YYYY h:mm TT')))) as New_Date;

LOAD * Inline [

Date

"Thu  3/9/2017 6:10 PM"

2/22/2017

];