> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
How to convert an integer date to a real date using OS/390 D
All you need is Oconv(int_field,"DYMD") in a Transformer stage.
There are no data types within DataStage; the recorded data types are user in interacting with databases. The Oconv() function has specific rules for wholly numeric date forms, of which the above solution makes use. Eight and six digits are regarded as "regular" dates (so you need the YMD to assert that your current locale order (DMY in NZ) is not to be followed) with four and two digits in the year respectively. Seven and five digits are interpreted as the ordinal day number within the year and the year number.
The cast function is not known to DataStage BASIC expressions. You could use it in the selecting SQL (but then the "cat" operator will need to be the "||" operator (unless "cat" is known to the SQL of DB2, which is outside my knowledge). But this would be in the user-defined SQL field, not in a column derivation subsequent to extraction.
(I assume "from 2 for 4" is a typo!)
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 01 October 2001 07:25
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
There are no data types within DataStage; the recorded data types are user in interacting with databases. The Oconv() function has specific rules for wholly numeric date forms, of which the above solution makes use. Eight and six digits are regarded as "regular" dates (so you need the YMD to assert that your current locale order (DMY in NZ) is not to be followed) with four and two digits in the year respectively. Seven and five digits are interpreted as the ordinal day number within the year and the year number.
The cast function is not known to DataStage BASIC expressions. You could use it in the selecting SQL (but then the "cat" operator will need to be the "||" operator (unless "cat" is known to the SQL of DB2, which is outside my knowledge). But this would be in the user-defined SQL field, not in a column derivation subsequent to extraction.
(I assume "from 2 for 4" is a typo!)
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 01 October 2001 07:25
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
There was an error in previous "solution". Shouldve read it before clicking Send! :-~
You need to convert the "integer form" into a DataStage internal form before converting it into an external form.
Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
You can also use substrings and concatenation. Syntax is: int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 01 October 2001 07:41
To: datastage-users@oliver.com
Cc: pko@clear.co.nz
Subject: RE: How to convert an integer date to a real date using OS/390 DataStage
All you need is Oconv(int_field,"DYMD") in a Transformer stage.
There are no data types within DataStage; the recorded data types are user in interacting with databases. The Oconv() function has specific rules for wholly numeric date forms, of which the above solution makes use. Eight and six digits are regarded as "regular" dates (so you need the YMD to assert that your current locale order (DMY in NZ) is not to be followed) with four and two digits in the year respectively. Seven and five digits are interpreted as the ordinal day number within the year and the year number.
The cast function is not known to DataStage BASIC expressions. You could use it in the selecting SQL (but then the "cat" operator will need to be the "||" operator (unless "cat" is known to the SQL of DB2, which is outside my knowledge). But this would be in the user-defined SQL field, not in a column derivation subsequent to extraction.
(I assume "from 2 for 4" is a typo!)
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 01 October 2001 07:25
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
You need to convert the "integer form" into a DataStage internal form before converting it into an external form.
Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
You can also use substrings and concatenation. Syntax is: int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 01 October 2001 07:41
To: datastage-users@oliver.com
Cc: pko@clear.co.nz
Subject: RE: How to convert an integer date to a real date using OS/390 DataStage
All you need is Oconv(int_field,"DYMD") in a Transformer stage.
There are no data types within DataStage; the recorded data types are user in interacting with databases. The Oconv() function has specific rules for wholly numeric date forms, of which the above solution makes use. Eight and six digits are regarded as "regular" dates (so you need the YMD to assert that your current locale order (DMY in NZ) is not to be followed) with four and two digits in the year respectively. Seven and five digits are interpreted as the ordinal day number within the year and the year number.
The cast function is not known to DataStage BASIC expressions. You could use it in the selecting SQL (but then the "cat" operator will need to be the "||" operator (unless "cat" is known to the SQL of DB2, which is outside my knowledge). But this would be in the user-defined SQL field, not in a column derivation subsequent to extraction.
(I assume "from 2 for 4" is a typo!)
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 01 October 2001 07:25
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
Hello Ray,
It does not work that way in OS/390. I believe it works in the server component.
I tried both of your options but both of them returned me with 100008 error code - An expression is required for the statement to be valid or complete.
My syntax is:
Oconv(Iconv(LM117T00_SELECT_01.LM117_TERM_DATE,"DYMD"),"D-YMD[4,2,2]")
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 01 October, 2001 9:46 a.m.
To: datastage-users@oliver.com
Cc: pko@clear.co.nz
Subject: RE: How to convert an integer date to a real date using OS/390 DataStage
There was an error in previous "solution". Shouldve read it before clicking Send! :-~
You need to convert the "integer form" into a DataStage internal form before converting it into an external form.
Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
You can also use substrings and concatenation. Syntax is: int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 01 October 2001 07:41
To: datastage-users@oliver.com
Cc: pko@clear.co.nz
Subject: RE: How to convert an integer date to a real date using OS/390 DataStage
All you need is Oconv(int_field,"DYMD") in a Transformer stage.
There are no data types within DataStage; the recorded data types are user in interacting with databases. The Oconv() function has specific rules for wholly numeric date forms, of which the above solution makes use. Eight and six digits are regarded as "regular" dates (so you need the YMD to assert that your current locale order (DMY in NZ) is not to be followed) with four and two digits in the year respectively. Seven and five digits are interpreted as the ordinal day number within the year and the year number.
The cast function is not known to DataStage BASIC expressions. You could use it in the selecting SQL (but then the "cat" operator will need to be the "||" operator (unless "cat" is known to the SQL of DB2, which is outside my knowledge). But this would be in the user-defined SQL field, not in a column derivation subsequent to extraction.
(I assume "from 2 for 4" is a typo!)
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 01 October 2001 07:25
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
It does not work that way in OS/390. I believe it works in the server component.
I tried both of your options but both of them returned me with 100008 error code - An expression is required for the statement to be valid or complete.
My syntax is:
Oconv(Iconv(LM117T00_SELECT_01.LM117_TERM_DATE,"DYMD"),"D-YMD[4,2,2]")
Cheers,
Paul.
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 01 October, 2001 9:46 a.m.
To: datastage-users@oliver.com
Cc: pko@clear.co.nz
Subject: RE: How to convert an integer date to a real date using OS/390 DataStage
There was an error in previous "solution". Shouldve read it before clicking Send! :-~
You need to convert the "integer form" into a DataStage internal form before converting it into an external form.
Oconv(Iconv(int_field,"DYMD"),"D-YMD[4,2,2]")
You can also use substrings and concatenation. Syntax is: int_field[1,4] : "-" : int_field[5,2] : "-" : int_field[7,2]
-----Original Message-----
From: Ray Wurlod [mailto:ray.wurlod@Informix.Com]
Sent: Monday, 01 October 2001 07:41
To: datastage-users@oliver.com
Cc: pko@clear.co.nz
Subject: RE: How to convert an integer date to a real date using OS/390 DataStage
All you need is Oconv(int_field,"DYMD") in a Transformer stage.
There are no data types within DataStage; the recorded data types are user in interacting with databases. The Oconv() function has specific rules for wholly numeric date forms, of which the above solution makes use. Eight and six digits are regarded as "regular" dates (so you need the YMD to assert that your current locale order (DMY in NZ) is not to be followed) with four and two digits in the year respectively. Seven and five digits are interpreted as the ordinal day number within the year and the year number.
The cast function is not known to DataStage BASIC expressions. You could use it in the selecting SQL (but then the "cat" operator will need to be the "||" operator (unless "cat" is known to the SQL of DB2, which is outside my knowledge). But this would be in the user-defined SQL field, not in a column derivation subsequent to extraction.
(I assume "from 2 for 4" is a typo!)
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Monday, 01 October 2001 07:25
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
Paul,
Try this:
cast int_field as date
I dont have access to DS 390 at the moment to test this out, but I think this will do what you want. I think you will have to make sure the data type of the target field is date (and allows nulls). I believe the target field will be set to null if the cast function can not translate the integer value to a valid date.
Mike
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Sunday, September 30, 2001 5:25 PM
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>
Try this:
cast int_field as date
I dont have access to DS 390 at the moment to test this out, but I think this will do what you want. I think you will have to make sure the data type of the target field is date (and allows nulls). I believe the target field will be set to null if the cast function can not translate the integer value to a valid date.
Mike
-----Original Message-----
From: Paul Ko [mailto:PKO@clear.co.nz]
Sent: Sunday, September 30, 2001 5:25 PM
To: datastage-users@oliver.com
Subject: How to convert an integer date to a real date using OS/390 DataStage
> Hello,
>
> There is a field in a DB2 table that is defined as integer. The field
> is currently storing a valid date. For example, 20010909.
>
> I am trying to use DataStage to convert this to a real date format.
> How can it be done using the OS/390 DataStage component.
>
> My idea is to use substring and cast function to cut and merge the
> integer field into a date field. However, DataStage does not accept
> my syntax.
>
> My syntax is:
> substring(cast(int_field as char) from 2 for 4) cat "-" cat
> substring(cast(int_field as char) from 6 for 2) cat "-" cat
> substring(cast(int_field as char) from 8 for 2)
>
> Please advise on solution.
>
> Cheers,
> Paul.
>