Coverting decimal field to db2 date?

[Ron]

Who knows how to convert a field with picture
decimal (8) to a db2 date?

--
Met vriendelijke groet
Ron van der Poel

[Mark A]

"Ron" <fa*******@wanadoo.nl> wrote in message
news:42***********************@news.wanadoo.nl...

Who knows how to convert a field with picture
decimal (8) to a db2 date?

--
Met vriendelijke groet
Ron van der Poel

If you have a host variable with PIC 9(8), and the format is YYYYMMDD (or
similar), and you want to use that host variable in an SQL statement, then
here is one way.

First, make sure the it is not defined as S9(8). If it is, move it to a
variable that is defined as PIC 9(8) to strip off the sign. The PIC 9(8)
target variable should be REDEFINED as PIC X(8), with separate subordinate
levels for the year, month, and day as individual variables X(4), X(2), and
X(2).

Then move the character year, month, and day variables to another variable
which has embedded dashes so that the final character filed is defined as
PIC X(10) as follows '2005-04-13'.

Then use the PIC X(10) variable as a host variable in the SQL statement.

[Ron]

it has to be done in one statement, so I have to use db2-functions
we're not dealing with host variables or cobol programming

--
Met vriendelijke groet
Ron van der Poel
"Mark A" <no****@nowhere.com> schreef in bericht
news:9P********************@comcast.com...

"Ron" <fa*******@wanadoo.nl> wrote in message
news:42***********************@news.wanadoo.nl...

Who knows how to convert a field with picture
decimal (8) to a db2 date?

--
Met vriendelijke groet
Ron van der Poel
If you have a host variable with PIC 9(8), and the format is YYYYMMDD (or
similar), and you want to use that host variable in an SQL statement, then
here is one way.

First, make sure the it is not defined as S9(8). If it is, move it to a
variable that is defined as PIC 9(8) to strip off the sign. The PIC 9(8)
target variable should be REDEFINED as PIC X(8), with separate subordinate
levels for the year, month, and day as individual variables X(4), X(2),

and X(2).

Then move the character year, month, and day variables to another variable
which has embedded dashes so that the final character filed is defined as
PIC X(10) as follows '2005-04-13'.

Then use the PIC X(10) variable as a host variable in the SQL statement.

[Serge Rielau]

Ron wrote:

it has to be done in one statement, so I have to use db2-functions
we're not dealing with host variables or cobol programming

--
Met vriendelijke groet
Ron van der Poel
"Mark A" <no****@nowhere.com> schreef in bericht
news:9P********************@comcast.com...

"Ron" <fa*******@wanadoo.nl> wrote in message
news:42***********************@news.wanadoo.nl.. .

Who knows how to convert a field with picture
decimal (8) to a db2 date?

--
Met vriendelijke groet
Ron van der Poel

If you have a host variable with PIC 9(8), and the format is YYYYMMDD (or
similar), and you want to use that host variable in an SQL statement, then
here is one way.

First, make sure the it is not defined as S9(8). If it is, move it to a
variable that is defined as PIC 9(8) to strip off the sign. The PIC 9(8)
target variable should be REDEFINED as PIC X(8), with separate subordinate
levels for the year, month, and day as individual variables X(4), X(2),

and

X(2).

Then move the character year, month, and day variables to another variable
which has embedded dashes so that the final character filed is defined as
PIC X(10) as follows '2005-04-13'.

Then use the PIC X(10) variable as a host variable in the SQL statement.

CREATE FUNCTION DEC8_2_DATE(arg DECIMAL(8, 0)) RETURNS DATE
CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION
RETURN (SELECT DATE(SUBSTR(numstr, 1, 4)
|| '-'
|| SUBSTR(numstr, 5, 2)
|| '-'
|| SUBSTR(numstr, 7, 2))
FROM TABLE(VALUES (CHAR(arg)))AS T(numstr));

VALUES DEC8_2_DATE(20050412);
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark A]

"Ron" <fa*******@wanadoo.nl> wrote in message
news:42***********************@news.wanadoo.nl...

it has to be done in one statement, so I have to use db2-functions
we're not dealing with host variables or cobol programming

If you are not dealing with COBOL, where did you get the term "picture". Is
it used in any other programming language?

[Tonkuma]

Another idea:

CREATE FUNCTION DEC8_2_DATE(arg DECIMAL(8, 0))
RETURNS DATE
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN DATE(TIMESTAMP(DIGITS(arg)||'000000'));