topic How to pull date and Time portion from TimeStamp Field in QlikView

How to pull date and Time portion from TimeStamp Field

taha_mansoor — Tue, 02 Feb 2016 14:54:34 GMT

Hi there,

Earlier,I used Date(Floor(TimeStampField)) as Date for Date field and Time(Frac(TimeStampField)) as Time for Time field and it worked fine but now its not working. May be due to changed TimeStamp field structure but what function needs to be used here. Data file and qvw is attached.

Regards,

Taha

Re: How to pull date and Time portion from TimeStamp Field

sunny_talwar — Tue, 02 Feb 2016 16:59:37 GMT

Try this:

Date:

LOAD Date(Floor(TimeStamp#(Left([Created On], 19), 'YYYY-MM-DD hh:mm:ss')),'DD/MM/YYYY') as Date,

Time(Frac(TimeStamp#(Left([Created On], 19), 'YYYY-MM-DD hh:mm:ss'))) as Time,

[Created On]

FROM

Date.xls

(biff, embedded labels, table is Sheet1$);

Re: How to pull date and Time portion from TimeStamp Field

taha_mansoor — Tue, 02 Feb 2016 18:58:01 GMT

sunindia‌

Thanks. It worked, however, its also giving the same result when using withour TimeStamp# function i.e. Time(Frac(Left([Created On], 19))) as Time. By the way, Whats the role of TimeStamp# function here ?

I noticed that all values im my TimeStamp field possess +00:00 at the right most side. Do you have any idea, what does that mean ?

Re: How to pull date and Time portion from TimeStamp Field

sunny_talwar — Tue, 02 Feb 2016 20:05:05 GMT

TimeStamp#() function is used when QlikView is not able to understand you TimeStamp field as TimeStamp. We can use TimeStamp#(), Dat#(), Time#() function to help QlikView understand that our date format is little different then what we have specified at the top using SET function.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';