topic Re: Format Date in QlikView

Format Date

fkeuroglian — Sun, 21 Jun 2015 17:37:37 GMT

Hi experts:

i want to apply a format date to a datetime wiht hours and second, but do not responde

i am doing Date#(left(Time,10),'DD/MM/YYYY') as DATE(i want the '/' to separate) but my result is 11.06.2015(take the "." as separator)

what could be?

other question, what could i separate, time from the original datetime(only using mid?)

attach qvd and qvw

thank you a lot

Fernando

Re: Format Date

swuehl — Sun, 21 Jun 2015 17:54:21 GMT

To parse the input field as Qv date using date#() function, you need to use the input field format as second argument.

Then you can format the QV date according your requirement using date() function:

Date(Date#(left(Time,10),'DD.MM.YYYY'),'DD/MM/YYYY') as DATE

Re: Format Date

maxgro — Sun, 21 Jun 2015 18:03:33 GMT

for time

time(frac(Date#(Time,'DD.MM.YYYY hh:mm:ss'))) as NEWTIME

Re: Format Date

swuehl — Sun, 21 Jun 2015 18:08:13 GMT

Have also a look into Henric's blog:

Get the Dates Right‌

The Date Function

Re: Format Date

MarcoWedel — Sun, 21 Jun 2015 21:10:17 GMT

Hi,

you could also create timestamps using an interpretation function (timestamp#) and use QlikView date/time functions to derive time and date information from this field (within a preceding load / using your DateFormat and TimestampFormat settings):

SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

tabData:
LOAD *,
    Time(Frac(Time_Timestamp)) as Time_Time,
    DayName(Time_Timestamp) as Time_Date,
    Time(Frac(Paidasfrom_Timestamp)) as Paidasfrom_Time,
    DayName(Paidasfrom_Timestamp) as Paidasfrom_Date;
LOAD Ticketnumber,
    Timestamp(Timestamp#(Time,'DD.MM.YYYY hh:mm:ss')) as Time_Timestamp,
    Timestamp(Timestamp#(Paidasfrom,'DD.MM.YYYY hh:mm:ss')) as Paidasfrom_Timestamp
FROM [https://community.qlik.com/servlet/JiveServlet/download/802701-171245/MovTicketQKV.20150611000000.20150611235959.EXP] (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

hope this helps

regards

Marco