Solved: Dateformat - Qlik Community

Report Inappropriate Content · ‎2013-03-14

Hi,

Am having date format as attached in the file. I want it to be converted into yyyy-mm-dd HH.MM.SS format.

Regards,

Tejavardhan

Report Inappropriate Content · ‎2013-03-14

Hi teja,

Test:

LOAD Date,
     KeepChar(left(mid(Date,5),3),Date) as Month,
     mid(Date,Index(Date,' ',3)-2,2) as Date1,
     mid(Date,Index(Date,' ',5)-14,9) as Time,
     mid(Date,Index(Date,' ',5),5) as Year,
     Sales
FROM
G:\Test1.xlsx
(ooxml, embedded labels, table is Sheet1);

Test2:

Load *,
Date1&'/'&Month&'/'&trim(Year)&' '&(Time) as Timestamp
Resident Test;
DROP Table Test;

PFA of Result Snapshot.

View solution in original post

er_mohit · ‎2013-03-14

DATE(DATE#(FIELDNAME,'DD-MM-YYYY hh:mm:ss'),'YYYY-MM-DD hh:mm:ss') as DATE

here in first its your field nformat and then write that format which you want....

Report Inappropriate Content · ‎2013-03-14

hi

try this

Date(Date#(fieldname,'ddd MMM DD hh:mm:ss YYYY'),'YYYY-MM-DD hh:mm:ss')

Report Inappropriate Content · ‎2013-03-14

Hi,

Its not Working.

Report Inappropriate Content · ‎2013-03-14

hi,

Am using this syntax

Date(Date#[created_at],'EEE MMM dd HH:mm:ss ZZZ YYYY'),'yyyy-mm-dd') as [created_date]

but its not working.

What exactlty is the denotion for the format dat i have attached in the previous document.

Regards,

Tejavardhan

Report Inappropriate Content · ‎2013-03-14

HI

IN YPUR SCRIPT

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

Date(Date#(fieldname,'WWWW MMM DD hh:mm:ss YYYY'),'YYYY-MM-DD hh:mm:ss')

sushil353 · ‎2013-03-14

Hi try this..

Please find attached

Report Inappropriate Content · ‎2013-03-14

Hi

Am using personnel edition i cant open .qvw format files. can u help me in other way

Regards,

Tejavardhan

sushil353 · ‎2013-03-14

Try script:

tab1:

LOAD * Inline

[

datefield

Fri Nov 30 23:01:26 +0000 2012

Fri Nov 30 23:01:29 +0000 2012

Fri Nov 30 23:01:31 +0000 2012

Fri Nov 30 23:01:34 +0000 2012

Fri Nov 30 23:01:36 +0000 2012

Fri Nov 30 23:01:37 +0000 2012

Fri Nov 30 23:01:38 +0000 2012

];

Temp1:

LOAD

mid(datefield,5,15) as first,

Right(datefield,4) as second

resident tab1;

Temp2:

LOAD

first &' '&second as test

Resident Temp1;

final:

LOAD

Date(Date#(test,'MMM DD hh:mm:ss YYYY'),'YYYY-MM-DD hh:mm:ss') as date_t

Resident Temp2;

DROP Tables Temp1,Temp2;

Report Inappropriate Content · ‎2013-03-14

hi sushil,

Is there any other way for this because am having 1lakh rows i cant create inline function for all those rows that is the problem.

Regards,

Tejavardhan

Dateformat

Related Topics