Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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
Highlighted

Hi Ashish, you can use the Date# function:

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

Highlighted
Contributor II
Contributor II

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

Highlighted

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

Highlighted
Champion III
Champion III

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

Highlighted
Contributor II
Contributor II

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