topic Re: How to convert date Field(timestamp) as date in QlikView

How to convert date Field(timestamp) as date

Lily — Sat, 16 Nov 2024 18:26:22 GMT

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.

Re: How to convert date Field(timestamp) as date

Anonymous — Fri, 26 Jun 2020 08:43:33 GMT

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

Re: How to convert date Field(timestamp) as date

Lily — Fri, 26 Jun 2020 09:20:39 GMT

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