topic Re: Convert String to Date & Time in QlikView

Convert String to Date & Time

manojkulkarni — Tue, 14 Mar 2017 08:25:40 GMT

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"

Re: Convert String to Date & Time

satheshreddy — Tue, 14 Mar 2017 08:50:24 GMT

‌Hi Manoj,

use

date(date#(),required date format)

REgards

SAthish

Re: Convert String to Date & Time

sunny_talwar — Tue, 14 Mar 2017 09:49:25 GMT

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

PrashantSangle — Tue, 14 Mar 2017 09:54:23 GMT

Hi,

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

Regards,

Re: Convert String to Date & Time

PrashantSangle — Tue, 14 Mar 2017 09:55:41 GMT

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

see alt() in help menu for details.

Regards

Re: Convert String to Date & Time

manojkulkarni — Tue, 14 Mar 2017 09:56:14 GMT

Not Working Prashant

Re: Convert String to Date & Time

sunny_talwar — Tue, 14 Mar 2017 09:56:38 GMT

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

sunny_talwar — Tue, 14 Mar 2017 09:57:41 GMT

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

Re: Convert String to Date & Time

manojkulkarni — Tue, 14 Mar 2017 10:00:32 GMT

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

sunny_talwar — Tue, 14 Mar 2017 10:04:05 GMT

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

];

Re: Convert String to Date & Time

PrashantSangle — Tue, 14 Mar 2017 10:14:48 GMT

Hi,

actually I never try this but i try

date(today(),'www DD/MM/YYYY hh:mm TT') which gives me 'Tue 14/03/2017 12:00 PM'

So I thought same format should work for date#(), But i checked it is not working.

might be we need to do some setting in ester egg.

Regards,

Prashant

Re: Convert String to Date & Time

sunny_talwar — Tue, 14 Mar 2017 10:15:51 GMT

Make sense.... Ya, I had trouble with weekday in Date# function.

Re: Convert String to Date & Time

manojkulkarni — Tue, 14 Mar 2017 12:06:30 GMT

Thanks Sunny , It worked