topic Re: Date in QlikView

Date

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

HI all

Currently my Date format for the field " Datefield" is like this

24-MAY-17 01.30.58.000000000 PM

i want to convert this this Datefield like 24-05-2017

How do i achaive this ?

Can someone please help me

Thanks in advance

Re: Date

its_anandrjs — Tue, 03 Oct 2017 05:25:32 GMT

Try to write this

LOAD *,Date(Date#(left(DateString,9),'DD-MMM-YY'),'DD-MM-YY') as NewDate;

LOAD * Inline

[

DateString

24-MAY-17 01.30.58.000000000 PM

];

Re: Date

its_anandrjs — Tue, 03 Oct 2017 05:27:34 GMT

Find the attached

LOAD

*,Date(Date#(left(DateString,9),'DD-MMM-YY'),'DD-MM-YY') as NewDate;

LOAD * Inline

[

DateString

24-MAY-17 01.30.58.000000000 PM

];

Re: Date

smilingjohn — Tue, 03 Oct 2017 05:30:06 GMT

Hi Anand

Thanks for the reply

I have many date field which are from 4 years ...

Cant we have a date conversion instead of above ?

I mean i want to convert this at the one line script itself ..

Like this below

Date#(Num(Datefield,'DD-MM-YYY') as Datefield

I hope u undertood what i am tryong to say

Thanks

Re: Date

its_anandrjs — Tue, 03 Oct 2017 05:36:02 GMT

From your date string 24-MAY-17 01.30.58.000000000 PM first you have to cut the date string that is 24-MAY-2017 and this will do for all rows in that field and then convert it into proper date for that is you need 24-05-17 this is also do for all rows.

Hope you are looking for this and this is what you are looking for.

LOAD

*,Date(Date#(left(DateString,9),'DD-MMM-YY'),'DD-MM-YY') as NewDate;

LOAD * Inline

[

DateString

24-MAY-17 01.30.58.000000000 PM

23-MAY-17 05.30.58.000000000 PM

22-MAY-17 08.30.58.000000000 PM

];

Re: Date

smilingjohn — Tue, 03 Oct 2017 05:37:40 GMT

Hi Anand

PLease find the attachement of the QVW file ..

I want the output for all the Datefields

In numbers like below .

01-12-2017 12:34:00

Thanks in advance

Re: Date

antoniotiman — Tue, 03 Oct 2017 05:48:43 GMT

Hi John,

may be this

Date(Date#(BillingDate,'DD-MMM-YY hh.mm.ss.fff TT'),'DD-MM-YYYY hh:mm:ss')

Regards,

Antonio

Re: Date

its_anandrjs — Tue, 03 Oct 2017 05:49:00 GMT

Try with Timestamp

Timestamp(Timestamp#(left(DateString,18),'DD-MMM-YY hh.mm.ss'),'DD-MM-YY hh:mm:ss') as NewDate

Re: Date

smilingjohn — Tue, 03 Oct 2017 05:56:42 GMT

Thanks Anand

You rock

That works but i need the out put with year 2017 .

where as now it is giving year like 17 ...

Re: Date

its_anandrjs — Tue, 03 Oct 2017 05:57:12 GMT

Find the attached

Re: Date

its_anandrjs — Tue, 03 Oct 2017 05:58:28 GMT

Close the thread buddy by appropriate answers and for references.

Re: Date

its_anandrjs — Tue, 03 Oct 2017 06:00:25 GMT

Ok thanks then try this

Timestamp(Timestamp#(left(DateString,18),'DD-MMM-YY hh.mm.ss'),'DD-MM-YYYY hh:mm:ss') as NewDate