Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everybody,
My client is using DB2 .So am developing qlikview dashboard with DB2 as Datasource.They have the date format
"CYYMMDD"
where C-Represents century .
I want this to be converted into the "DD /MM /YYYY".Currently am using below expression for this
=date(date#(right(Field Name,6),'YYMMDD'),'DD/MM/YY')
Currently am converting('CYYMMDD' to 'DD/MM/YY')
which is working fine but i want to convert 'CYYMMDD' to 'DD /MM /YYYY'
Thanks in Advance,
Priya
Hi,
Another possibility:
=Date(AddYears(Date(Date#('19'&Right(DateField,6),'YYYYMMDD'),'DD/MM/YYYY')
,100*Left(DateField,1)),'DD/MM/YYYY')
Regards
Hi,
Can you post one sample date and expected output or Sample QVW.
-shruti
=Date(Date#(Right(YourDateField,6),'YYMMDD'),'DD/MM/YYYY')
like below..
140115 will convert to 15/01/2014
=Date(Date#(Right('1140115',6),'YYMMDD'),'DD/MM/YYYY')
You can use the right function right() to remove C and then convert the same to DD/MM/YY
Hi Priya,
I think you are on the right way. Here is some sql script for try with other method
SQL Select
ACTDT AS ActualDate,
Cast(Case When SubString(Right('00' + Cast(ACTDT As Varchar), 7), 1, 1) = '0' Then '19' Else '20' End + SubString(Right('00' + Cast(ACTDT As Varchar), 7), 2, 2) + SubString(Right('00' + Cast(ACTDT AS As Varchar), 7), 4, 4) As DateTime) As [NewDate] From TableName;
Regards,
Sokkorn
try
=date(date#(right(Field Name,8),'YYYYMMDD'),'DD/MM/YYYY')
You may not need to truncate the field using right(xxx,8) depending on your data
Hi,
Try like this
where vDate = 19140129 - 19 Century 14 year 01 month and 29 Day CYYMMDD
=Date(MakeDate(Left(Right(vDate, 6),2) + Mid(vDate, 1, Len(vDate) -6) * 100, Left(Right(vDate, 4),2), Right(vDate, 2)), 'DD/MM/YYYY')
Regards,
Jagan.
That is done ..please read my query thoroughly,
using the below expression,
=date(date#(right(Field Name,6),'YYMMDD'),'DD/MM/YY')
am getting it 'DD/MM/YY'.But am actually looking for 'DD/MM/YYYY' which is bit tricky.
The single digit "C" indicates the century. 0=1900, 1=2000, etc.
You can't ignore the century as qlikview will make a default assumption about the century if you parse just the YYMMDD part. Years >=61 are considered to be in 1900, Years<61 are considered to be 2000. That could be an incorrect assumption for your dates, such as a birthdate in 1945.
So to correctly parse the century:
=date(
makedate(
1900 + (left('0450831',1)*100) + mid('0450831',2,2) // Year
,mid('0450831',4,2) // Month
,mid('0450831',6,2) // Day
)
,'DD/MM/YYYY')
=31/08/1945
-Rob
Hi,
Another possibility:
=Date(AddYears(Date(Date#('19'&Right(DateField,6),'YYYYMMDD'),'DD/MM/YYYY')
,100*Left(DateField,1)),'DD/MM/YYYY')
Regards