Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
ashishtams
Contributor II
Contributor II

convert string to date format in script

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

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

5 Replies
rubenmarin

Hi Ashish, you can use the Date# function:

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

ashishtams
Contributor II
Contributor II
Author

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

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.

mov
Employee
Employee

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
Contributor II
Contributor II
Author

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