Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi,
use this exp
= DATE(MakeDate(Right(Trim([ Date]),4),SubField([ Date],'/',2),left([ Date],2)),'DD/MM/YYYY') AS DATE,
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
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
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