Change date from CYYMMDD to DD/MM/YY ?

[#1]

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

[#2 · Accepted solution]

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

[#3]

Hi,

use this exp

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

[#4]

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

[#5]

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

[#6]

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