How to convert date Field(timestamp) as date

[Lily]

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. 

[Anonymous · Accepted solution]

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')

[Lily]

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