topic Load DateTimeStamp as Date only in QlikView

Load DateTimeStamp as Date only

Thu, 24 Oct 2013 17:28:09 GMT

How to load only the date portion of a Timestamp. I tried using 'DayStart(DTSTAMP) as T_Day, but it comes across with 12:00:00 AM. I want it to show as MM/DD/YYYY only. When I tried the date# interpretation, it came across as text (I assume since the values were left-justified when shown in a list box).

Re: Load DateTimeStamp as Date only

Thu, 24 Oct 2013 17:32:50 GMT

try Date(Floor(DTSSTAMP), 'MM/DD/YYYY') AS [MyDateTimeStamp]

Re: Load DateTimeStamp as Date only

MayilVahanan — Thu, 24 Oct 2013 17:32:53 GMT

Try like this

Date(Date(DTSTAMP, 'MM/DD/YYY hh:mm:ss TT'),'MM/DD/YYYY')

Date(Date#(DTSTAMP, 'MM/DD/YYY hh:mm:ss TT'),'MM/DD/YYYY')

note :'MM/DD/YYY hh:mm:ss TT' must matches your timestamp format

Re: Load DateTimeStamp as Date only

Anonymous — Thu, 24 Oct 2013 17:40:43 GMT

All correct, just adding one more:

date(DayStart(DTSTAMP), 'MM/DD/YYYY') as T_Day

Re: Load DateTimeStamp as Date only

gareth_muir — Thu, 03 May 2018 17:26:27 GMT

I tried to use these but my qlik app kept coming up with a blank where I wanted an MM-YY list.

Eventually I had to separate out the MM/DD/YYYY text string inside of the Timestamp generated by my ERP, and use the DATE syntax. I did it with a left search formula which worked great -

Date(Monthstart(Left([inDateAdded],10)),'MM-YY') as Month_Scraped, //Default date format is MM/DD/YYYY hh:mm:ss.ffff