How to convert DB2 date format of CYYMMDD to DD/MM/YYYY on load

[martin_hamilton]

Hi I am connecting to a DB2 using SQL and the dates are being returned in the format CYYMMDD and I wish to convert to DD/MM/YY.

Is there a standard to do this?

Thanks

Martin

[arulsettu]

post some sample data

check this

How to convert date format "CYYMMDD" to "DD /MM... | Qlik Community

Change date from CYYMMDD to DD/MM/YY ? | Qlik Community

[martin_hamilton]

Hi

The data in its raw format is:

DateField

1160101

1160801

1160901

1150101

1150102

I am assuming I would drop off the leading character and then transform the date. But wasnt sure if that was possible in a single load script?

Thanks

Martin

[ToniKautto · Accepted solution]

With the function below you control the date format through the format variable DateFormat.

Date(Date#(Right(DateField, 6), 'YYMMDD'))

[vcanale]

Hi,

Try:
Date(Right(DateField, 6), 'DD/MM/YYYY')

[martin_hamilton]

That didnt quite work but i have done some research on SQL and managed to convert the field into a string format of DD/MM/YYYY using the below:

substr(cast([FIELD]as char (7)), 6, 2)|| '/' || substr(cast([FIELD]as char (7)), 4, 2)|| '/20' || substr(cast([FIELD]as char (7)), 2, 2) as newdate

I have tried to then reformat the field to a date type column but am struggling with that.

Thanks

Martin

[vcanale]

or better:
date(date#(right(DateField,6),'YYMMDD'),'DD/MM/YYYY')

[vcanale]

testing
=date(date#(right('1160101',6),'YYMMDD'),'DD/MM/YYYY')

in a Text Obj, it looks fine..
just use the FieldName instead of '1160101' in the Load

[ToniKautto]

I should work. Did you set the DateFormat variable to your expected output format in the beginning of the script?

SET DateFormat = 'DD/MM/YYYY';

I would think your SQL solution looks more complex than it would in QlikView, but any solution that works is a feasible solution. If you want to resolve this in QlikView, I would suggest that you break the expression down to see where the conversions fails for you.

Right(DateField, 6) is expected to return the six most right characters from DateField.

The 6 chars string is the input to the Date#() function, which returns a value that looks like YYMMDDut will have the date integer value. Date() takes the integer as in out and formats the text to format defined in DateFormat.

Date(Date#(Right(DateField, 6), 'YYMMDD'))

If the loaded value form data base actually is a number, then this will fails. Most often it will then be enough to just format form number to date.

Date(DateField)

[martin_hamilton]

Hi Vincenza

This is just returning blanks when included in the load