topic Convert Timestamp to Date in QlikView

Convert Timestamp to Date

Wed, 09 Dec 2015 08:08:17 GMT

Hi All,

I need to convert the timestamp 'Apr 21 2015 3:45:33:000PM' to the date format '4/21/2015' .

Kindly help me to achieve the same.

Thanks in adavnce for your help.

Regards,

Krishnan G

Gysbert_Wassenaar — Sat, 12 Dec 2015 14:11:54 GMT

Try: Date(Date#(Left('Apr 21 2015 3:45:33:000PM',12),'MMM DD YYYY'),'M/D/YYYY')

Note, there are two spaces between the month Apr and the day 21. These two spaces need to be in the date format string of the Date# function too.

maxgro — Sat, 12 Dec 2015 14:26:40 GMT

Date(floor(Date#('Apr 21 2015 3:45:33:000PM', 'MMM DD YYYY h:mm:ss:ffftt')), 'M/D/YYYY')

floor to remove the time part

sunny_talwar — Sat, 12 Dec 2015 14:53:36 GMT

Even this might be helpful if there are inconsistent spaces in your timestamp field:

=Date(Date#(Left(PurgeChar('Apr 21 2015 3:45:33:000PM', ' '), 9), 'MMMDDYYYY'))

here you can replace the manual entry with your date timestamp field in the script:

Date(Date#(Left(PurgeChar(TimeStampField, ' '), 9), 'MMMDDYYYY')) as Date