topic Re: Date format in QlikView

Date format

Wed, 17 Sep 2014 10:17:02 GMT

Hi all,

I have a Date field ENTRY_DATE coming from database in the format of 1,110,105 i.e CYYMMDD

i.e 1-C, 11-YY (2011), 01-MM(Jan), 05-DD

I want to show them as YYYY-MM-DD

Using Date(Right(ENTRY_DATE,6)) i could achieve 110105..but the Dates are not correct.

Any suggestions?

Thanks in advance

Re: Date format

PrashantSangle — Wed, 17 Sep 2014 10:19:13 GMT

Use Date(Date#(Right(ENTRY_DATE,6),'YYMMDD'),'YYYY-MM-DD')

Regards

Re: Date format

MK_QSL — Wed, 17 Sep 2014 10:21:55 GMT

Date(Date#(Right(ENTRY_DATE,5),'YYMMDD'),'YYYY-MM-DD') as ENTRY_DATE

Re: Date format

Wed, 17 Sep 2014 10:31:44 GMT

Thanks for the quick reply.

Any idea about Timestamp format with always TT as AM? Like below

Timestamp(Timestamp#(Right(ENTRY_DATE,5),'YYMMDD'),'YYYY-MM-DD hh:mm:ss TT') as ENTRY_DATE?

Re: Date format

PrashantSangle — Wed, 17 Sep 2014 10:38:12 GMT

Hi,

When you use

this expression

Timestamp(Timestamp#(Right(ENTRY_DATE,5),'YYMMDD'),'YYYY-MM-DD hh:mm:ss TT')

But your Right(ENTRY_DATE,5) giving only YYMMDD formated date then

Timestamp() consider other parameter like "hh:mm:ss " as "00:00:00"

and according to 00 hour, 00 min and 00 sec your TT as 'AM'.

Regards,

Re: Date format

MK_QSL — Wed, 17 Sep 2014 10:41:31 GMT

=TimeStamp(TimeStamp#(Date(Date#(Right(ENTRY_DATE,6),'YYMMDD')) & ' 00:00:00'),'YYYY-MM-DD hh:mm:ss TT')

Re: Date format

Wed, 17 Sep 2014 11:06:42 GMT

Hi Dude try this

Date(Date#(Right(date,7),'YYM,MDD'),'YY-MM-DD') as date1 Resident table;

and please close the thread

Re: Date format

Wed, 17 Sep 2014 11:06:57 GMT

This is another Timestamp field. where i want them as AM always no matter when you run the script.

Not sure why the Timestamp format is showing as A9 instead of AM for TT..

Rest are fine though..

Thanks.

Re: Date format

Wed, 17 Sep 2014 11:10:03 GMT

hi Gtripathy i think what u have selected as correct answer is not giving the exact answer what ur expecting , please try my example and compare the results

Re: Date format

Wed, 17 Sep 2014 11:30:04 GMT

I got it.

Timestamp(Date(Date#(Right(ENTRY_DATE,6),'YYMMDD'),'YYYY-MM-DD hh:mm:ss AM')) as ENTRY_TIMESTAMP

Closing the thread.

Thanks.