convert string to date format in script

[ashishtams]

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

[rubenmarin]

Hi Ashish, you can use the Date# function:

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

[ashishtams]

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

[rubenmarin · Accepted solution]

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.

[Anonymous]

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

[ashishtams]

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