How to convert date format "CYYMMDD" to "DD /MM /YYYY"?

[priyav24]

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

[#2]

Hi,

Can you post one sample date and expected output or Sample QVW.

-shruti

[MK_QSL]

=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')

[ashwanin]

You can  use the right function right() to remove C and then convert the same to DD/MM/YY

[Sokkorn]

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

[Colin-Albert]

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

[jagan]

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.

[priyav24]

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.

[rwunderlich]

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

[#10 · Accepted solution]

Hi,

Another possibility:

=Date(AddYears(Date(Date#('19'&Right(DateField,6),'YYYYMMDD'),'DD/MM/YYYY')

          ,100*Left(DateField,1)),'DD/MM/YYYY')

Regards