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 III
Contributor III

How to convert date Field(timestamp) as date

Hi , 

I'm a newbie for Qlikview. I've tried several examples from forum, but still not success to convert my date field which is in timestamp to get date only. 

Date field: 9/28/2017 3:39 am UTC

Result should be like this: 2017-09-28

Below is script that I've tried: 

1) Date(Date#([Created On],'MM/DD/YYYY h:mm TT [.fff]'),'YYYY-MM-DD') as 'Date',
2) Date(Timestamp#([Created On],'MM/DD/YYYY h:mm TT [.fff]'),'YYYY-MM-DD') as 'Date',
3) Date(Floor(TimeStamp#([Created On], 'MM/DD/YYYY h:mm TT [.fff]')), 'YYYY-MM-DD') as 'Date',
4) Date(Floor(Date#([Created On], 'MM/DD/YYYY h:mm TT [.fff]')), 'YYYY-MM-DD'))) as 'Date',

Please help me to achieve this. 

Thanks. 

Labels (4)
1 Solution

Accepted Solutions
Highlighted
Anonymous
Not applicable

I tried several format options and i guess that UTC bothers the format options (when i change to GMT it works)

So when you only need the date you may subtract the date out of the string

=date(date#(left('9/28/2017 3:39 am UTC',FindOneOf('9/28/2017 3:39 am UTC',' ')),'mm/dd/yyyy'),'yyyy-mm-dd')
or using your field
=date(date#(left([Created On],FindOneOf([Created On],' ')),'mm/dd/yyyy'),'yyyy-mm-dd')

View solution in original post

2 Replies
Highlighted
Anonymous
Not applicable

I tried several format options and i guess that UTC bothers the format options (when i change to GMT it works)

So when you only need the date you may subtract the date out of the string

=date(date#(left('9/28/2017 3:39 am UTC',FindOneOf('9/28/2017 3:39 am UTC',' ')),'mm/dd/yyyy'),'yyyy-mm-dd')
or using your field
=date(date#(left([Created On],FindOneOf([Created On],' ')),'mm/dd/yyyy'),'yyyy-mm-dd')

View solution in original post

Highlighted
Contributor III
Contributor III

Thanks. It's working for me. But just wondering why did you use 'left()' function? 

Lily_0-1593163041421.png