topic Removing Time from timestamp in QlikView

Removing Time from timestamp

Thu, 07 Jul 2011 04:37:53 GMT

Hello All,

I need a clarification about text - Date format. I have extracted data from csv file with a field called record_date which has the timestamp values in text format. Take a look at the below pic.

I want to remove the time from record_date Please help me in this.

Thanks in advance.

Removing Time from timestamp

Thu, 07 Jul 2011 05:20:29 GMT

Hi Rajan,

If you want to convert from text/string into date format, you can use the DATE# function, which I think will result in the time data to be lost.

So in your case, I think it should be like this:

DATE#( record_date, 'M/D/YYYY HH:MM' ) AS date;

I haven't actually tested if you can force DATE# on a timestamp string, but if QlikView can't, then you can just use TIMESTAMP# combined with DATE:

DATE( TIMESTAMP#( record_date, 'M/D/YYYY HH:MM' ), 'M/D/YYYY' ) AS date;

Of course you can just use only TIMESTAMP# and set your default Numbering Format of that field to just show the date (via Document Settings or individually on each object/chart you're using that field in).

--Kennie

Re: Removing Time from timestamp

johnw — Fri, 08 Jul 2011 00:28:33 GMT

In the format string, you need hh:mm instead of HH:MM. MM stands for the month, not the minute. Lower case gives you the minute. When reading in a timestamp, use the timestamp# function. You could use string functions to strip off the time, then use the date# function, but I'd just use the timestamp# function. However, you can't just wrap that in a date() function. Date() is a FORMATTING function, and will not change the underlying data, so you will still have a timestamp including the time, even if it displays as a date. Use floor() or daystart() to remove the fractional part. Or if by "remove the time" your meant "create a time field", that's basically the same thing, but with time() and frac().

,date(floor(timestamp#(record_date,'M/D/YYYY hh:mm'))) as record_date
,time(frac (timestamp#(record_date,'M/D/YYYY hh:mm'))) as record_time

Re: Removing Time from timestamp

Fri, 08 Jul 2011 02:07:14 GMT

Ah yes, thanks for correcting me for the time format string as well as clearing up the date() part.

Re: Removing Time from timestamp

Fri, 08 Jul 2011 02:34:39 GMT

Hi John,

Finally, I got it. Thanks. But Its not working with floor function.when I omitted it, I got the correct values.

Re: Removing Time from timestamp

Fri, 08 Jul 2011 02:44:47 GMT

Hi Kennie,

Thanks to you also. I got an initial idea from u. but some of the values were null because of that HH:MM. I have corrected with help of John. Thanks to you Kennie.

Re: Removing Time from timestamp

Mon, 23 Dec 2013 09:35:39 GMT

sorry to bring up a 'dead' topic.

I am also dealing with a timestamp field, which I need to split in date and time, so to check the time against a cutoff time.

I was applying the formulas suggested by Witherspoon and crushing my head because I could not get any data out of it.

I thought the problem was in the source data, cos I could not really get a hang if it was DD-MM-YYYY, DD/MM/YY etc.etc. (it changed depending on the programs I used to open the dataset)

then I tried these formulas

num(floor(CreationDate)) as numDateFLOOR,
num(frac(CreationDate)) as numDateFRAC,

I was 'surpised' it worked like a charm on the first try

and I could directly compare them with the cutoff time, mapped like this

'15:00' as Cutoff

just posting it here in case someone is facing the same troubles!

Re: Removing Time from timestamp

Tue, 15 Apr 2014 15:27:28 GMT

Exactly what I have been looking for, thank you!