topic Re: Timestamp to Date in QlikView

Timestamp to Date

paulsparrow — Wed, 25 Nov 2020 16:16:04 GMT

Tried so many examples posted on board and can't get what I want. Excel sheet is bringing in date/timestamp of 31/01/2017 6:22:58 PM

Need to get it to: 'YYYY-MM-DD'

Tried using subfield: DATE#(SubField([Survey Timestamp],' ',1),'YYYY-MM-DD') as [Survey Timestamp1],

Tried using floor: Date(Floor(Date#([Survey Timestamp],'DDMMYYYY hh:mm:ss'))) as [Survey Timestamp2]

Tried using Date/Date# DATE(DATE#([Survey Timestamp],'DDMMYYYY hh:mm:ss'),'YYYY-MM-DD') as [Survey Timestamp3],

sunny_talwar — Fri, 10 Mar 2017 18:41:06 GMT

1) Are there two spaces between Date and Time part?

2) You need to use TT for AM/PM time formats.

May be this

Date(Floor(TimeStamp#([Survey Timestamp], 'DD/MM/YYYY h:mm:ss TT')), 'YYYY-MM-DD') as [Survey Date]

sunny_talwar — Fri, 10 Mar 2017 18:41:34 GMT

Also look here:

paulsparrow — Fri, 10 Mar 2017 18:46:26 GMT

Nope. Produces blank. This is as close as I can get

DATE#(SubField([Survey Timestamp],' ',1),'YYYY-MM-DD') as [Survey Timestamp1], gives me the numerical value like 42736.3009375

sunny_talwar — Fri, 10 Mar 2017 18:48:33 GMT

It seems that your timestamp is already read as datetime field... try this

Date(Floor([Survey Timestamp]), 'YYYY-MM-DD') as [Survey Date]

MarcoWedel — Sun, 12 Mar 2017 17:56:31 GMT

or maybe just

DayName([Survey Timestamp])

given you set the required default DateFormat.

hope this helps

regards

Marco