topic Re: Convert a string to Date & Time in QlikView

Convert a string to Date & Time

MalcolmCICWF — Wed, 02 Dec 2015 22:45:54 GMT

I was wondering, what would be the easiest way to convert this string into a date and time with the AM/PM on it? I don't know who though it was a good idea to output an '@' in the middle of this. Right now I am doing a 'left' and taking out the date part and converting, but I am having issues removing the time and formatting. There must be an easier way...

Re: Convert a string to Date & Time

swuehl — Wed, 02 Dec 2015 22:51:31 GMT

Just use the timestamp interpretation function Timestamp#() with an appropriate format code:

=Timestamp(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT'))

edit: If you want to separate date and time, just use the timestamp returned:

=Date(Floor(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

=Time(Frac(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

Re: Convert a string to Date & Time

tamilarasu — Thu, 03 Dec 2015 02:19:21 GMT

Another solution could be,

=Date(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm TT))

=Time(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm tt'),'hh:mm TT')

You can also use PurgeChar syntax to remove the @ symbol form your string field.