topic Re: convert string to date format in script in QlikView

convert string to date format in script

ashishtams — Tue, 28 Apr 2015 10:23:26 GMT

Hello All,

I am new to Qlikview, need help with date conversion.

Currently i am connecting Qlikview to DB2 database. The date column(timestamp_key) in my table is having values like this. its of a varchar type in database.

20140821151230

20140822161235

means the format is

YYYYMMDDHHMISS

now I want to use this in qlikview and see as a normal date(DD-MM-YYYY).

I tried to change the format in the sql using the below function and it works in the sql, however when i use the same in the qlikview script then it does not accept this

date(timestamp_format([timestamp_key],YYYYMMDDHH24MISS))

please help.

Thanks

Ashish Tamboli

Re: convert string to date format in script

rubenmarin — Tue, 28 Apr 2015 14:19:36 GMT

Hi Ashish, you can use the Date# function:

=Date(Date#('20140821151230', 'YYYYMMDDhhmmss'), 'DD-MM-YYYY')

Re: convert string to date format in script

ashishtams — Wed, 29 Apr 2015 13:42:30 GMT

Hi Ruben

I tried what you have mentioned, its not working.

I achieved it using below formula

date(trunc(to_date([timestamp_key],'YYYYMMDDHH24MISS')))

Thanks,

Ashish Tamboli

Re: convert string to date format in script

rubenmarin — Wed, 29 Apr 2015 13:51:18 GMT

Hi Asihs, just to clarify the difference...mine was for use in LOAD sentence or qlikview object, retrieve the varchar from database and transforming it when loading in script.

Yours is to use in the 'SELECT' sentence, when the query is executed on the database.

Re: convert string to date format in script

Anonymous — Wed, 29 Apr 2015 13:55:14 GMT

Ashish,

The answer from RubenMarin‌ is correct - it is using QV functionality. Your answer is in DB2 syntax. So it depends where exactly in the script you convert the date.

Regards,

Michael

Re: convert string to date format in script

ashishtams — Thu, 30 Apr 2015 02:56:16 GMT

Ruben/Michael

Thanks for the clarification. Really Appreciate for sharing the difference. It will help a lot

Yes Ruben, your answer is correct. I tried it in the LOAD part of the script.

Thanks,

Ashish Tamboli