Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Change date from CYYMMDD to DD/MM/YY ?

Hi Guys

New to QlikView (obviously ) and i've been looking at examples to change the date format date(date#(replace.... but i cant get it to convert our dates from our iSeries into a decent format. We have it in CYYMMDD so 1111118 would be todays today. I'd prefer to change it to 18/11/11. Sadly i've been unable to work out the syntax properly to do this. Anyone have any idea's ?

Cheers !

Adam

1 Solution

Accepted Solutions
Not applicable

Re: Change date from CYYMMDD to DD/MM/YY ?

Hi Adam,

what is C at the starting?

Anyway what you can do it cut the C and take YYMMDD as field with right() function  and then later convert with Date() function to DD/MM/YY

Regards

Sravan

5 Replies
Not applicable

Re: Change date from CYYMMDD to DD/MM/YY ?

Hi Adam,

what is C at the starting?

Anyway what you can do it cut the C and take YYMMDD as field with right() function  and then later convert with Date() function to DD/MM/YY

Regards

Sravan

Not applicable

Re: Change date from CYYMMDD to DD/MM/YY ?

Hi,

use this exp

= DATE(MakeDate(Right(Trim([ Date]),4),SubField([ Date],'/',2),left([ Date],2)),'DD/MM/YYYY') AS DATE,

Not applicable

Change date from CYYMMDD to DD/MM/YY ?

since the date you are receiving is a number not a date so you can't apply date functions directly, you have to trim the number as:

mid(CYYMMDD,2,2) as year;

mid(CYYMMDD,4,2) as month;

mid(CYYMMDD,6,2) as day;

Date(MakeDate('20' & $(year),$(month),$(day)),'DD/MM/YYYY') as date;

you get the required date.

regards

Not applicable

Change date from CYYMMDD to DD/MM/YY ?

Ah righty, i thought the C might have been throwing it out, on our system thats Century. Like thats helpful

I'll give it a whirl and report back.

Thanks for your help !

Kind Regards

Adam

Not applicable

Change date from CYYMMDD to DD/MM/YY ?

Thanks to you guys i got it working, Thank you all for your suggestions. Ended up with:

=date(date#(right(DTSO40,6),'YYMMDD'),'DD/MM/YY')

Which changes 1111118 to 18/11/11

Kind Regards

Adam