How to pull date and Time portion from TimeStamp Field

[taha_mansoor]

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 

[sunny_talwar · Accepted solution]

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$);

[taha_mansoor]

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 ?

[sunny_talwar]

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';