Timestamp to Date

[paulsparrow]

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]

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]

or

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

[sunny_talwar]

Also look here:

Why don’t my dates work?

QlikView Date fields

Get the Dates Right

[paulsparrow]

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 · Accepted solution]

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

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

[MarcoWedel]

or maybe just

DayName([Survey Timestamp])

given you set the required default DateFormat.

hope this helps

regards

Marco