Format Date

[fkeuroglian]

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

[swuehl · Accepted solution]

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

[maxgro]

for time

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

[swuehl]

Have also a look into Henric's blog:

Get the Dates Right‌

The Date Function

[MarcoWedel]

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.20...] (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

hope this helps

regards

Marco