DB2 11 for z/OS Timestamp value inside SQL statement

Daniel Luksetich

DB2 11 for z/OS Timestamp value inside SQL statement
This may be a silly question, but I am having trouble with a COBOL
program. It is a batch program and it uses a Softbase product for the
attachment facility. A statement is failing with a SQLCODE -180, which
indicates the timestamp is invalid.

Here is the one and only predicate
CRTD_TS BETWEEN
CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
AND CHAR(CURRENT_DATE) || '-00.00.00.000000'

So, in SPUFI and in IBM Data Studio the statement completes
successfully. In COBOL it does not.

This works in COBOL:

CRTD_TS BETWEEN
TIMESTAMP(CURRENT_DATE - 1 DAY)
AND TIMESTAMP(CURRENT_DATE)

This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:

CRTD_TS BETWEEN
TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')

any thoughts on why this is happening?

Thanks,
Dan

Armando Arranja

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Daniel Luksetich)
Hi Dan,

A silly suggestion, try using CONCAT instead of the || operator. I've ran into trouble with the COBOL compiler before.

Best regards,
Armando



-----Original Message-----
From: Daniel Luksetich [mailto:[login to unmask email]
Sent: 27 de dezembro de 2016 17:10
To: [login to unmask email]
Subject: [DB2-L] - DB2 11 for z/OS Timestamp value inside SQL statement

This may be a silly question, but I am having trouble with a COBOL program. It is a batch program and it uses a Softbase product for the attachment facility. A statement is failing with a SQLCODE -180, which indicates the timestamp is invalid.

Here is the one and only predicate
CRTD_TS BETWEEN
CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
AND CHAR(CURRENT_DATE) || '-00.00.00.000000'

So, in SPUFI and in IBM Data Studio the statement completes successfully. In COBOL it does not.

This works in COBOL:

CRTD_TS BETWEEN
TIMESTAMP(CURRENT_DATE - 1 DAY)
AND TIMESTAMP(CURRENT_DATE)

This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:

CRTD_TS BETWEEN
TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000') AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')

any thoughts on why this is happening?

Thanks,
Dan

-----End Original Message-----







AVISO DE CONFIDENCIALIDADE
Esta mensagem e quaisquer ficheiros anexos a ela contêm informação confidencial e destinam-se a uso exclusivo a quem nela conste como destinatário. Caso não seja o destinatário desta mensagem, fica informado que recebeu esta mensagem por engano, e que qualquer utilização, distribuição, reencaminhamento ou outra forma de revelação a outrem, impressão, ou cópia desta mensagem é expressamente proibida, agradecendo que a elimine do seu sistema e informe o remetente ou o Banco BPI,S.A.
O Banco BPI, S.A. utiliza software anti-vírus. Não obstante ter tomado todas as precauções, o Banco BPI, S.A. não garante que esta mensagem e os seus anexos não contenham vírus. É, assim, da responsabilidade do destinatário assegurar que esta mensagem e seus anexos são submetidos a detector de vírus antes da sua utilização. Alerta-se ainda para o facto de as mensagens transmitidas por este meio poderem ser interceptadas, corrompidas, destruídas ou não chegarem ao destino ou chegarem com atraso.

Sam Baugh

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Daniel Luksetich)
Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich <[login to unmask email]>
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

Venkat Srinivasan

RE: DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Sam Baugh)


Sam, I suppose you meant pre compiler option....

Venkat


In Reply to Sam Baugh:

Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich <[login to unmask email]>
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

Daniel Luksetich

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Armando Arranja)
Thanks for the suggestion, but it did not work.
Dan

On 12.27.2016 09:43, Armando Arranja wrote:
> Hi Dan,
>
> A silly suggestion, try using CONCAT instead of the || operator. I've
> ran into trouble with the COBOL compiler before.
>
> Best regards,
> Armando
>
>
>
> -----Original Message-----
> From: Daniel Luksetich [mailto:[login to unmask email]
> Sent: 27 de dezembro de 2016 17:10
> To: [login to unmask email]
> Subject: [DB2-L] - DB2 11 for z/OS Timestamp value inside SQL statement
>
> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000') AND
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>
>
>
>
>
>
> AVISO DE CONFIDENCIALIDADE
> Esta mensagem e quaisquer ficheiros anexos a ela contêm informação
> confidencial e destinam-se a uso exclusivo a quem nela conste como
> destinatário. Caso não seja o destinatário desta mensagem, fica
> informado que recebeu esta mensagem por engano, e que qualquer
> utilização, distribuição, reencaminhamento ou outra forma de revelação
> a outrem, impressão, ou cópia desta mensagem é expressamente proibida,
> agradecendo que a elimine do seu sistema e informe o remetente ou o
> Banco BPI,S.A.
> O Banco BPI, S.A. utiliza software anti-vírus. Não obstante ter tomado
> todas as precauções, o Banco BPI, S.A. não garante que esta mensagem e
> os seus anexos não contenham vírus. É, assim, da responsabilidade do
> destinatário assegurar que esta mensagem e seus anexos são submetidos
> a detector de vírus antes da sua utilização. Alerta-se ainda para o
> facto de as mensagens transmitidas por este meio poderem ser
> interceptadas, corrompidas, destruídas ou não chegarem ao destino ou
> chegarem com atraso.
>
> -----End Original Message-----

Daniel Luksetich

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Venkat Srinivasan)
Considering that this is all internal to DB2 would a precompiler option really affect this statement?

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 12:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement


Sam, I suppose you meant pre compiler option....
Venkat

In Reply to Sam Baugh:
Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

-----End Original Message-----

Sam Baugh

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Armando Arranja)
It would be interesting to see the DISPLAY of what CHAR(CURRENT DATE)
returns in COBOL. I know we have some code using CHAR(CURRENT DATE,ISO),
not sure why, but it is not there by accident (Our default is USA format).

There is also the TRUNC_TIMESTAMP or TIMESTAMP function that could be used:

select trunc_timestamp(current timestamp), trunc_timestamp(current
timestamp - 1 day) from sysibm.sysdummy1 with ur

or

select timestamp(current date,'00:00'), timestamp(current date - 1
day,'00:00'), timestamp(current date,'24:00') from sysibm.sysdummy1 with ur


On Tue, Dec 27, 2016 at 11:43 AM, Armando Arranja <[login to unmask email]>
wrote:

> Hi Dan,
>
> A silly suggestion, try using CONCAT instead of the || operator. I've ran
> into trouble with the COBOL compiler before.
>
> Best regards,
> Armando
>
>
>
> -----Original Message-----
> From: Daniel Luksetich [mailto:[login to unmask email]
> Sent: 27 de dezembro de 2016 17:10
> To: [login to unmask email]
> Subject: [DB2-L] - DB2 11 for z/OS Timestamp value inside SQL statement
>
> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes successfully.
> In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000') AND
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>
>
>
>
>
>
> AVISO DE CONFIDENCIALIDADE
> Esta mensagem e quaisquer ficheiros anexos a ela contêm informação
> confidencial e destinam-se a uso exclusivo a quem nela conste como
> destinatário. Caso não seja o destinatário desta mensagem, fica informado
> que recebeu esta mensagem por engano, e que qualquer utilização,
> distribuição, reencaminhamento ou outra forma de revelação a outrem,
> impressão, ou cópia desta mensagem é expressamente proibida, agradecendo
> que a elimine do seu sistema e informe o remetente ou o Banco BPI,S.A.
> O Banco BPI, S.A. utiliza software anti-vírus. Não obstante ter tomado
> todas as precauções, o Banco BPI, S.A. não garante que esta mensagem e os
> seus anexos não contenham vírus. É, assim, da responsabilidade do
> destinatário assegurar que esta mensagem e seus anexos são submetidos a
> detector de vírus antes da sua utilização. Alerta-se ainda para o facto de
> as mensagens transmitidas por este meio poderem ser interceptadas,
> corrompidas, destruídas ou não chegarem ao destino ou chegarem com atraso.
>
> -----End Original Message-----
>
>

Joe Geller

RE: DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Daniel Luksetich)

Dan,

It's not all internal to DB2.  There is a precompiler option for the date format.  From the application programming manual:

DATE(ISO|USA |EUR|JIS|LOCAL)Specifies that date output should always be returned in a particular format, regardless of the format that is specified as the location default.

The default is specified in the field DATE FORMAT on Application Programming Defaults Panel 2 during installation.

The default format is determined by the installation defaults of the system where the program is bound, not by the installation defaults of the system where the program is precompiled.

Normally you wouldn't specify the date format option on a precompile and just take the subsystem default.  But, the Cobol compile proc might have been setup with it.  Check the proc to see if it is there.

Joe

In Reply to Daniel Luksetich:

Considering that this is all internal to DB2 would a precompiler option really affect this statement?

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 12:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement


Sam, I suppose you meant pre compiler option....
Venkat

In Reply to Sam Baugh:
Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

-----End Original Message-----

Sam Baugh

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Joe Geller)
Just had to share this, was searching code to find if we did something
similar in one of our COBOL programs and came across this SQL:

SELECT CHAR(BEGIN_DATE,ISO)
,RIGHT(DIGITS(YEAR (BEGIN_DATE +6 DAYS
)),4) || '-' ||
RIGHT(DIGITS(MONTH(BEGIN_DATE +6 DAYS
)),2) || '-' ||
RIGHT(DIGITS(DAY(BEGIN_DATE +6 DAYS
)),2) END_DATE
INTO :DB-BEG-DATE
,:DB-END-DATE
FROM x.xxxxx

They could have simply used CHAR(BEGIN_DATE +6 DAYS,ISO). Those darn
developers.

I did find code where they did something similar in SQL statement in COBOL
program: CREATE_TIMESTAMP BETWEEN
TIMESTAMP(CHAR(X.BEGIN_DATE,ISO)||'-00.00.00.000000') AND
TIMESTAMP(CHAR(X.END_DATE,ISO)||'-24.00.00.000000'). I'm assuming this
runs correctly. Not sure that the surrounding TIMESTAMP function was
necessary.

On Tue, Dec 27, 2016 at 1:22 PM, Joe Geller <[login to unmask email]> wrote:

> Dan,
>
> It's not all internal to DB2. There is a precompiler option for the date
> format. From the application programming manual:
>
> DATE(ISO|USA |EUR|JIS|LOCAL)Specifies that date output should always be
> returned in a particular format, regardless of the format that is specified
> as the location default.
>
> The default is specified in the field DATE FORMAT on Application
> Programming Defaults Panel 2 during installation.
>
> The default format is determined by the installation defaults of the
> system where the program is bound, not by the installation defaults of the
> system where the program is precompiled.
>
> Normally you wouldn't specify the date format option on a precompile and
> just take the subsystem default. But, the Cobol compile proc might have
> been setup with it. Check the proc to see if it is there.
>
> Joe
>
> In Reply to Daniel Luksetich:
>
> Considering that this is all internal to DB2 would a precompiler option
> really affect this statement?
>
> From: Venkat Srinivasan [mailto:[login to unmask email]
> <[login to unmask email]>
> Sent: Tuesday, December 27, 2016 12:00 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement
>
>
> Sam, I suppose you meant pre compiler option....
> Venkat
>
> In Reply to Sam Baugh:
> Perhaps COBOL is returning the date in USA format?
>
> On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
> wrote:
>
> > This may be a silly question, but I am having trouble with a COBOL
> > program. It is a batch program and it uses a Softbase product for the
> > attachment facility. A statement is failing with a SQLCODE -180, which
> > indicates the timestamp is invalid.
> >
> > Here is the one and only predicate
> > CRTD_TS BETWEEN
> > CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> > AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
> >
> > So, in SPUFI and in IBM Data Studio the statement completes
> > successfully. In COBOL it does not.
> >
> > This works in COBOL:
> >
> > CRTD_TS BETWEEN
> > TIMESTAMP(CURRENT_DATE - 1 DAY)
> > AND TIMESTAMP(CURRENT_DATE)
> >
> > This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
> >
> > CRTD_TS BETWEEN
> > TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> > AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> >
> > any thoughts on why this is happening?
> >
> > Thanks,
> > Dan
> >
> > -----End Original Message-----
> >
> >
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>

Daniel Luksetich

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Sam Baugh)
Add ISO to the CHAR function did the trick, thanks!

I am having trouble finding documentation on the DATE BIND option, I don’t have access to the precompiler options, and I can’t find anything about date format in SYSPACKAGE. I might be going nuts but if someone can point me to this information it would be helpful. I found DATE as a BIND parameter in online documentation, but not in my PDF version.

Thanks,
Dan

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 1:41 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement

Just had to share this, was searching code to find if we did something similar in one of our COBOL programs and came across this SQL:

SELECT CHAR(BEGIN_DATE,ISO)
,RIGHT(DIGITS(YEAR (BEGIN_DATE +6 DAYS
)),4) || '-' ||
RIGHT(DIGITS(MONTH(BEGIN_DATE +6 DAYS
)),2) || '-' ||
RIGHT(DIGITS(DAY(BEGIN_DATE +6 DAYS
)),2) END_DATE
INTO :DB-BEG-DATE
,:DB-END-DATE
FROM x.xxxxx
They could have simply used CHAR(BEGIN_DATE +6 DAYS,ISO). Those darn developers.
I did find code where they did something similar in SQL statement in COBOL program: CREATE_TIMESTAMP BETWEEN TIMESTAMP(CHAR(X.BEGIN_DATE,ISO)||'-00.00.00.000000') AND TIMESTAMP(CHAR(X.END_DATE,ISO)||'-24.00.00.000000'). I'm assuming this runs correctly. Not sure that the surrounding TIMESTAMP function was necessary.

On Tue, Dec 27, 2016 at 1:22 PM, Joe Geller <[login to unmask email]> wrote:
Dan,
It's not all internal to DB2. There is a precompiler option for the date format. From the application programming manual:
DATE(ISO|USA |EUR|JIS|LOCAL)Specifies that date output should always be returned in a particular format, regardless of the format that is specified as the location default.
The default is specified in the field DATE FORMAT on Application Programming Defaults Panel 2 during installation.
The default format is determined by the installation defaults of the system where the program is bound, not by the installation defaults of the system where the program is precompiled.
Normally you wouldn't specify the date format option on a precompile and just take the subsystem default. But, the Cobol compile proc might have been setup with it. Check the proc to see if it is there.
Joe

In Reply to Daniel Luksetich:
Considering that this is all internal to DB2 would a precompiler option really affect this statement?

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 12:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement


Sam, I suppose you meant pre compiler option....
Venkat

In Reply to Sam Baugh:
Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

-----End Original Message-----

-----End Original Message-----



-----End Original Message-----

Sam Baugh

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Daniel Luksetich)
I think it is determined by the zparm setting, DATE(USA). Not something I
would consider changing, way too much code based on USA format.

On Tue, Dec 27, 2016 at 1:58 PM, Daniel L Luksetich <[login to unmask email]>
wrote:

> Add ISO to the CHAR function did the trick, thanks!
>
>
>
> I am having trouble finding documentation on the DATE BIND option, I don’t
> have access to the precompiler options, and I can’t find anything about
> date format in SYSPACKAGE. I might be going nuts but if someone can point
> me to this information it would be helpful. I found DATE as a BIND
> parameter in online documentation, but not in my PDF version.
>
>
>
> Thanks,
>
> Dan
>
>
>
> *From:* Sam Baugh [mailto:[login to unmask email]
> *Sent:* Tuesday, December 27, 2016 1:41 PM
> *To:* [login to unmask email]
>
> *Subject:* [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL
> statement
>
>
>
> Just had to share this, was searching code to find if we did something
> similar in one of our COBOL programs and came across this SQL:
>
> SELECT CHAR(BEGIN_DATE,ISO)
> ,RIGHT(DIGITS(YEAR (BEGIN_DATE +6 DAYS
> )),4) || '-' ||
> RIGHT(DIGITS(MONTH(BEGIN_DATE +6 DAYS
> )),2) || '-' ||
> RIGHT(DIGITS(DAY(BEGIN_DATE +6 DAYS
> )),2) END_DATE
> INTO :DB-BEG-DATE
> ,:DB-END-DATE
> FROM x.xxxxx
>
> They could have simply used CHAR(BEGIN_DATE +6 DAYS,ISO). Those darn
> developers.
>
> I did find code where they did something similar in SQL statement in COBOL
> program: CREATE_TIMESTAMP BETWEEN TIMESTAMP(CHAR(X.BEGIN_DATE,ISO)||'-00.00.00.000000')
> AND TIMESTAMP(CHAR(X.END_DATE,ISO)||'-24.00.00.000000'). I'm assuming
> this runs correctly. Not sure that the surrounding TIMESTAMP function was
> necessary.
>
>
>
> On Tue, Dec 27, 2016 at 1:22 PM, Joe Geller <[login to unmask email]> wrote:
>
> Dan,
>
> It's not all internal to DB2. There is a precompiler option for the date
> format. From the application programming manual:
>
> DATE(ISO|USA |EUR|JIS|LOCAL)Specifies that date output should always be
> returned in a particular format, regardless of the format that is specified
> as the location default.
>
> The default is specified in the field DATE FORMAT on Application
> Programming Defaults Panel 2 during installation.
>
> The default format is determined by the installation defaults of the
> system where the program is bound, not by the installation defaults of the
> system where the program is precompiled.
>
> Normally you wouldn't specify the date format option on a precompile and
> just take the subsystem default. But, the Cobol compile proc might have
> been setup with it. Check the proc to see if it is there.
>
> Joe
>
> In Reply to Daniel Luksetich:
>
> Considering that this is all internal to DB2 would a precompiler option
> really affect this statement?
>
> From: Venkat Srinivasan [mailto:[login to unmask email]
> <[login to unmask email]>
> Sent: Tuesday, December 27, 2016 12:00 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement
>
>
> Sam, I suppose you meant pre compiler option....
> Venkat
>
> In Reply to Sam Baugh:
> Perhaps COBOL is returning the date in USA format?
>
> On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
> wrote:
>
> > This may be a silly question, but I am having trouble with a COBOL
> > program. It is a batch program and it uses a Softbase product for the
> > attachment facility. A statement is failing with a SQLCODE -180, which
> > indicates the timestamp is invalid.
> >
> > Here is the one and only predicate
> > CRTD_TS BETWEEN
> > CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> > AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
> >
> > So, in SPUFI and in IBM Data Studio the statement completes
> > successfully. In COBOL it does not.
> >
> > This works in COBOL:
> >
> > CRTD_TS BETWEEN
> > TIMESTAMP(CURRENT_DATE - 1 DAY)
> > AND TIMESTAMP(CURRENT_DATE)
> >
> > This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
> >
> > CRTD_TS BETWEEN
> > TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> > AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> >
> > any thoughts on why this is happening?
> >
> > Thanks,
> > Dan
> >
> > -----End Original Message-----
> >
> >
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Daniel Luksetich

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Sam Baugh)
Our date format in the system parameters is ISO, which explains why the statement works everywhere but in COBOL. It has to be a precompiler setting.
Thanks,
Dan

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 2:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement

I think it is determined by the zparm setting, DATE(USA). Not something I would consider changing, way too much code based on USA format.

On Tue, Dec 27, 2016 at 1:58 PM, Daniel L Luksetich <[login to unmask email]> wrote:
Add ISO to the CHAR function did the trick, thanks!

I am having trouble finding documentation on the DATE BIND option, I don’t have access to the precompiler options, and I can’t find anything about date format in SYSPACKAGE. I might be going nuts but if someone can point me to this information it would be helpful. I found DATE as a BIND parameter in online documentation, but not in my PDF version.

Thanks,
Dan

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 1:41 PM
To: [login to unmask email]

Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement

Just had to share this, was searching code to find if we did something similar in one of our COBOL programs and came across this SQL:

SELECT CHAR(BEGIN_DATE,ISO)
,RIGHT(DIGITS(YEAR (BEGIN_DATE +6 DAYS
)),4) || '-' ||
RIGHT(DIGITS(MONTH(BEGIN_DATE +6 DAYS
)),2) || '-' ||
RIGHT(DIGITS(DAY(BEGIN_DATE +6 DAYS
)),2) END_DATE
INTO :DB-BEG-DATE
,:DB-END-DATE
FROM x.xxxxx
They could have simply used CHAR(BEGIN_DATE +6 DAYS,ISO). Those darn developers.
I did find code where they did something similar in SQL statement in COBOL program: CREATE_TIMESTAMP BETWEEN TIMESTAMP(CHAR(X.BEGIN_DATE,ISO)||'-00.00.00.000000') AND TIMESTAMP(CHAR(X.END_DATE,ISO)||'-24.00.00.000000'). I'm assuming this runs correctly. Not sure that the surrounding TIMESTAMP function was necessary.

On Tue, Dec 27, 2016 at 1:22 PM, Joe Geller <[login to unmask email]> wrote:
Dan,
It's not all internal to DB2. There is a precompiler option for the date format. From the application programming manual:
DATE(ISO|USA |EUR|JIS|LOCAL)Specifies that date output should always be returned in a particular format, regardless of the format that is specified as the location default.
The default is specified in the field DATE FORMAT on Application Programming Defaults Panel 2 during installation.
The default format is determined by the installation defaults of the system where the program is bound, not by the installation defaults of the system where the program is precompiled.
Normally you wouldn't specify the date format option on a precompile and just take the subsystem default. But, the Cobol compile proc might have been setup with it. Check the proc to see if it is there.
Joe

In Reply to Daniel Luksetich:
Considering that this is all internal to DB2 would a precompiler option really affect this statement?

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 12:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement


Sam, I suppose you meant pre compiler option....
Venkat

In Reply to Sam Baugh:
Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----



-----End Original Message-----

Horacio Villa

DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Sam Baugh)
More instructive would be to see the DISPLAY of CURRENT TIMESTAMP, to know
what to code instead '-00.00.00.000000'.

Horacio Villa



From: Sam Baugh <[login to unmask email]>
To: [login to unmask email]
Date: 12/27/2016 04:19 PM
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL
statement



It would be interesting to see the DISPLAY of what CHAR(CURRENT DATE)
returns in COBOL. I know we have some code using CHAR(CURRENT DATE,ISO),
not sure why, but it is not there by accident (Our default is USA format).

There is also the TRUNC_TIMESTAMP or TIMESTAMP function that could be
used:

select trunc_timestamp(current timestamp), trunc_timestamp(current
timestamp - 1 day) from sysibm.sysdummy1 with ur

or

select timestamp(current date,'00:00'), timestamp(current date - 1
day,'00:00'), timestamp(current date,'24:00') from sysibm.sysdummy1 with
ur


On Tue, Dec 27, 2016 at 11:43 AM, Armando Arranja <[login to unmask email]>
wrote:
Hi Dan,

A silly suggestion, try using CONCAT instead of the || operator. I've ran
into trouble with the COBOL compiler before.

Best regards,
Armando



-----Original Message-----
From: Daniel Luksetich [mailto:[login to unmask email]
Sent: 27 de dezembro de 2016 17:10
To: [login to unmask email]
Subject: [DB2-L] - DB2 11 for z/OS Timestamp value inside SQL statement

This may be a silly question, but I am having trouble with a COBOL
program. It is a batch program and it uses a Softbase product for the
attachment facility. A statement is failing with a SQLCODE -180, which
indicates the timestamp is invalid.

Here is the one and only predicate
CRTD_TS BETWEEN
CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
AND CHAR(CURRENT_DATE) || '-00.00.00.000000'

So, in SPUFI and in IBM Data Studio the statement completes successfully.
In COBOL it does not.

This works in COBOL:

CRTD_TS BETWEEN
TIMESTAMP(CURRENT_DATE - 1 DAY)
AND TIMESTAMP(CURRENT_DATE)

This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:

CRTD_TS BETWEEN
TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000') AND
TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')

any thoughts on why this is happening?

Thanks,
Dan

-----End Original Message-----







AVISO DE CONFIDENCIALIDADE
Esta mensagem e quaisquer ficheiros anexos a ela contêm informação
confidencial e destinam-se a uso exclusivo a quem nela conste como
destinatário. Caso não seja o destinatário desta mensagem, fica informado
que recebeu esta mensagem por engano, e que qualquer utilização,
distribuição, reencaminhamento ou outra forma de revelação a outrem,
impressão, ou cópia desta mensagem é expressamente proibida, agradecendo
que a elimine do seu sistema e informe o remetente ou o Banco BPI,S.A.
O Banco BPI, S.A. utiliza software anti-vírus. Não obstante ter tomado
todas as precauções, o Banco BPI, S.A. não garante que esta mensagem e os
seus anexos não contenham vírus. É, assim, da responsabilidade do
destinatário assegurar que esta mensagem e seus anexos são submetidos a
detector de vírus antes da sua utilização. Alerta-se ainda para o facto de
as mensagens transmitidas por este meio poderem ser interceptadas,
corrompidas, destruídas ou não chegarem ao destino ou chegarem com atraso.

-----End Original Message-----




Site Links: View post online View mailing list online Start new thread
via email Unsubscribe from this mailing list Manage your subscription


This email has been sent to: [login to unmask email]
** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
---> Anaheim, California, April 30 - May 04, 2017 <---
http://www.idug.org/na

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Sam Baugh

RE: DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Daniel Luksetich)

Looks like the option should be something like CBL SQL("DATE(USA)")  if included in COBOL source program, likely first line in source.  Otherwise, the compile JCL should show where the option was set.

Venkat Srinivasan

RE: DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Daniel Luksetich)

The fact that you got a different format with ISO coded on char function as an argument confirms your pre-compiler setting is date(usa) or something else other than iso.

The pre-compiler option itself is stored in dbrm headers mapped by dsnxpopt. It also goes into packstmt.

Venkat   
 
In Reply to Daniel Luksetich:

Our date format in the system parameters is ISO, which explains why the statement works everywhere but in COBOL. It has to be a precompiler setting.
Thanks,
Dan

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 2:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement

I think it is determined by the zparm setting, DATE(USA). Not something I would consider changing, way too much code based on USA format.

On Tue, Dec 27, 2016 at 1:58 PM, Daniel L Luksetich <[login to unmask email]> wrote:
Add ISO to the CHAR function did the trick, thanks!

I am having trouble finding documentation on the DATE BIND option, I don’t have access to the precompiler options, and I can’t find anything about date format in SYSPACKAGE. I might be going nuts but if someone can point me to this information it would be helpful. I found DATE as a BIND parameter in online documentation, but not in my PDF version.

Thanks,
Dan

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 1:41 PM
To: [login to unmask email]

Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement

Just had to share this, was searching code to find if we did something similar in one of our COBOL programs and came across this SQL:

SELECT CHAR(BEGIN_DATE,ISO)
,RIGHT(DIGITS(YEAR (BEGIN_DATE +6 DAYS
)),4) || '-' ||
RIGHT(DIGITS(MONTH(BEGIN_DATE +6 DAYS
)),2) || '-' ||
RIGHT(DIGITS(DAY(BEGIN_DATE +6 DAYS
)),2) END_DATE
INTO :DB-BEG-DATE
,:DB-END-DATE
FROM x.xxxxx
They could have simply used CHAR(BEGIN_DATE +6 DAYS,ISO). Those darn developers.
I did find code where they did something similar in SQL statement in COBOL program: CREATE_TIMESTAMP BETWEEN TIMESTAMP(CHAR(X.BEGIN_DATE,ISO)||'-00.00.00.000000') AND TIMESTAMP(CHAR(X.END_DATE,ISO)||'-24.00.00.000000'). I'm assuming this runs correctly. Not sure that the surrounding TIMESTAMP function was necessary.

On Tue, Dec 27, 2016 at 1:22 PM, Joe Geller <[login to unmask email]> wrote:
Dan,
It's not all internal to DB2. There is a precompiler option for the date format. From the application programming manual:
DATE(ISO|USA |EUR|JIS|LOCAL)Specifies that date output should always be returned in a particular format, regardless of the format that is specified as the location default.
The default is specified in the field DATE FORMAT on Application Programming Defaults Panel 2 during installation.
The default format is determined by the installation defaults of the system where the program is bound, not by the installation defaults of the system where the program is precompiled.
Normally you wouldn't specify the date format option on a precompile and just take the subsystem default. But, the Cobol compile proc might have been setup with it. Check the proc to see if it is there.
Joe

In Reply to Daniel Luksetich:
Considering that this is all internal to DB2 would a precompiler option really affect this statement?

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 12:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement


Sam, I suppose you meant pre compiler option....
Venkat

In Reply to Sam Baugh:
Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----



-----End Original Message-----

Venkat Srinivasan

RE: DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Sam Baugh)

Co processor use case is SQL (opts) in the first line as you describe and in pre compiler it will be in exec pgm=dsnhpc,parm=' '.

Venkat

In Reply to Sam Baugh:

Looks like the option should be something like CBL SQL("DATE(USA)")  if included in COBOL source program, likely first line in source.  Otherwise, the compile JCL should show where the option was set.

Joe Geller

RE: DB2 11 for z/OS Timestamp value inside SQL statement
(in response to Venkat Srinivasan)

In 2009 Roy Boxwell very kindly posted the layout and values for dsnxopt right here in DB2-L

http://www.idug.org/p/fo/et/thread=34122

Joe


In Reply to Venkat Srinivasan:

The fact that you got a different format with ISO coded on char function as an argument confirms your pre-compiler setting is date(usa) or something else other than iso.

The pre-compiler option itself is stored in dbrm headers mapped by dsnxpopt. It also goes into packstmt.

Venkat   
 
In Reply to Daniel Luksetich:

Our date format in the system parameters is ISO, which explains why the statement works everywhere but in COBOL. It has to be a precompiler setting.
Thanks,
Dan

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 2:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement

I think it is determined by the zparm setting, DATE(USA). Not something I would consider changing, way too much code based on USA format.

On Tue, Dec 27, 2016 at 1:58 PM, Daniel L Luksetich <[login to unmask email]> wrote:
Add ISO to the CHAR function did the trick, thanks!

I am having trouble finding documentation on the DATE BIND option, I don’t have access to the precompiler options, and I can’t find anything about date format in SYSPACKAGE. I might be going nuts but if someone can point me to this information it would be helpful. I found DATE as a BIND parameter in online documentation, but not in my PDF version.

Thanks,
Dan

From: Sam Baugh [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 1:41 PM
To: [login to unmask email]

Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement

Just had to share this, was searching code to find if we did something similar in one of our COBOL programs and came across this SQL:

SELECT CHAR(BEGIN_DATE,ISO)
,RIGHT(DIGITS(YEAR (BEGIN_DATE +6 DAYS
)),4) || '-' ||
RIGHT(DIGITS(MONTH(BEGIN_DATE +6 DAYS
)),2) || '-' ||
RIGHT(DIGITS(DAY(BEGIN_DATE +6 DAYS
)),2) END_DATE
INTO :DB-BEG-DATE
,:DB-END-DATE
FROM x.xxxxx
They could have simply used CHAR(BEGIN_DATE +6 DAYS,ISO). Those darn developers.
I did find code where they did something similar in SQL statement in COBOL program: CREATE_TIMESTAMP BETWEEN TIMESTAMP(CHAR(X.BEGIN_DATE,ISO)||'-00.00.00.000000') AND TIMESTAMP(CHAR(X.END_DATE,ISO)||'-24.00.00.000000'). I'm assuming this runs correctly. Not sure that the surrounding TIMESTAMP function was necessary.

On Tue, Dec 27, 2016 at 1:22 PM, Joe Geller <[login to unmask email]> wrote:
Dan,
It's not all internal to DB2. There is a precompiler option for the date format. From the application programming manual:
DATE(ISO|USA |EUR|JIS|LOCAL)Specifies that date output should always be returned in a particular format, regardless of the format that is specified as the location default.
The default is specified in the field DATE FORMAT on Application Programming Defaults Panel 2 during installation.
The default format is determined by the installation defaults of the system where the program is bound, not by the installation defaults of the system where the program is precompiled.
Normally you wouldn't specify the date format option on a precompile and just take the subsystem default. But, the Cobol compile proc might have been setup with it. Check the proc to see if it is there.
Joe

In Reply to Daniel Luksetich:
Considering that this is all internal to DB2 would a precompiler option really affect this statement?

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Tuesday, December 27, 2016 12:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 11 for z/OS Timestamp value inside SQL statement


Sam, I suppose you meant pre compiler option....
Venkat

In Reply to Sam Baugh:
Perhaps COBOL is returning the date in USA format?

On Tue, Dec 27, 2016 at 11:09 AM, Daniel Luksetich
wrote:

> This may be a silly question, but I am having trouble with a COBOL
> program. It is a batch program and it uses a Softbase product for the
> attachment facility. A statement is failing with a SQLCODE -180, which
> indicates the timestamp is invalid.
>
> Here is the one and only predicate
> CRTD_TS BETWEEN
> CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000'
> AND CHAR(CURRENT_DATE) || '-00.00.00.000000'
>
> So, in SPUFI and in IBM Data Studio the statement completes
> successfully. In COBOL it does not.
>
> This works in COBOL:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CURRENT_DATE - 1 DAY)
> AND TIMESTAMP(CURRENT_DATE)
>
> This does NOT work in COBOL, but does in SPUFI and IBM Data Studio:
>
> CRTD_TS BETWEEN
> TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
> AND TIMESTAMP(CHAR(CURRENT_DATE - 1 DAY) || '-00.00.00.000000')
>
> any thoughts on why this is happening?
>
> Thanks,
> Dan
>
> -----End Original Message-----
>
>

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----



-----End Original Message-----