Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

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

Re: Convert String to Date & Time

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

];

12 Replies
satheshreddy
Contributor III

Re: Convert String to Date & Time

‌Hi Manoj,

use

date(date#(),required date format)

REgards

SAthish

Re: Convert String to Date & Time

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

Re: Convert String to Date & Time

Hi,

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Convert String to Date & Time

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.
Partner
Partner

Re: Convert String to Date & Time

Not Working Prashant

Re: Convert String to Date & Time

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

Re: Convert String to Date & Time

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

Partner
Partner

Re: Convert String to Date & Time

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

Re: Convert String to Date & Time

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

];