Syntax of the string representation of a datetime value is incorrect SQLSTATE=22007

[Brenda]

We are currently switching to DB2/AIX. I am modifying my sqrs to work
on this new platform. (We are currently on Oracle). I am having a
problem with an sqr that has a reference to a variable for a date
field. I have copied that section of the select statement below.

You can see the old code that I used for Oracle--that works
great--which I have commented out and the new line I added just below.

When this sqr runs, it throws the 'Syntax of the string representation
of a datetime value is incorrect' error. That tells me that 'AND
Z.ACCOUNTING_DT = $ACCOUNTING_DT' is obviously wrong, however I don't
know how to fix. This gets even more complicated because what this
sqr does is load data from a .csv file and when it gets to the field
where the date is, we set that to the variable $ACCOUNTING_DT. Oracle
has no problem understanding it, but DB2 does.

Any ideas out there on how I can resolve this?

FROM PS_BI_ACCT_ENTRY Z
WHERE Z.BUSINESS_UNIT = $BUSINESS_UNIT
AND Z.INVOICE = $INVOICE
AND Z.LINE_SEQ_NUM = $LINE_SEQ_NUM
!002237..AND Z.ACCOUNTING_DT = TO_DATE($ACCOUNTING_DT,'YYYY-MM-DD')
AND Z.ACCOUNTING_DT = $ACCOUNTING_DT !002237..
AND Z.ACCT_ENTRY_TYPE = 'RR'
AND Z.DISC_SUR_LVL = 0
AND Z.DISC_SUR_ID = ' '
AND Z.TAX_AUTHORITY_CD = ' '
END-SELECT
Any suggestions would be very much appreciated!

Thanks, Brenda

[Mark A]

"Brenda" <bl*********@yahoo.com> wrote in message
news:19*************************@posting.google.co m...

We are currently switching to DB2/AIX. I am modifying my sqrs to work
on this new platform. (We are currently on Oracle). I am having a
problem with an sqr that has a reference to a variable for a date
field. I have copied that section of the select statement below.

You can see the old code that I used for Oracle--that works
great--which I have commented out and the new line I added just below.

When this sqr runs, it throws the 'Syntax of the string representation
of a datetime value is incorrect' error. That tells me that 'AND
Z.ACCOUNTING_DT = $ACCOUNTING_DT' is obviously wrong, however I don't
know how to fix. This gets even more complicated because what this
sqr does is load data from a .csv file and when it gets to the field
where the date is, we set that to the variable $ACCOUNTING_DT. Oracle
has no problem understanding it, but DB2 does.

Any ideas out there on how I can resolve this?

FROM PS_BI_ACCT_ENTRY Z
WHERE Z.BUSINESS_UNIT = $BUSINESS_UNIT
AND Z.INVOICE = $INVOICE
AND Z.LINE_SEQ_NUM = $LINE_SEQ_NUM
!002237..AND Z.ACCOUNTING_DT = TO_DATE($ACCOUNTING_DT,'YYYY-MM-DD')
AND Z.ACCOUNTING_DT = $ACCOUNTING_DT !002237..
AND Z.ACCT_ENTRY_TYPE = 'RR'
AND Z.DISC_SUR_LVL = 0
AND Z.DISC_SUR_ID = ' '
AND Z.TAX_AUTHORITY_CD = ' '
END-SELECT
Any suggestions would be very much appreciated!

Thanks, Brenda

What does the data look like on the CSV file for date.

[--CELKO--]

>> Any ideas out there on how I can resolve this? <<

You might want to look at the text file you are importing to see how
it is formatted. Oracle has a different date range than anyone else
and does not default to ISO-8601 formats ('yyyy-mm-dd'). Standard
SQL-92 only allows ISO-8601 temporal data.

[Brenda]

jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...

Any ideas out there on how I can resolve this? <<

You might want to look at the text file you are importing to see how
it is formatted. Oracle has a different date range than anyone else
and does not default to ISO-8601 formats ('yyyy-mm-dd'). Standard
SQL-92 only allows ISO-8601 temporal data.

I checked the csv file and the date is 2003-09-25 which is the correct
format. Do you think it is because when it reads the .csv file I set
that date to a variable ($ACCOUNTING_DT) and it needs me to do
something else to that value when it reads it? I don't know what else
to look at. Any other ideas would be great.

Thanks, Brenda

[Brenda]

"Mark A" <[e-mail]> wrote in message news:<PZ***************@news.uswest.net>...

"Brenda" <bl*********@yahoo.com> wrote in message
news:19*************************@posting.google.co m...

We are currently switching to DB2/AIX. I am modifying my sqrs to work
on this new platform. (We are currently on Oracle). I am having a
problem with an sqr that has a reference to a variable for a date
field. I have copied that section of the select statement below.

You can see the old code that I used for Oracle--that works
great--which I have commented out and the new line I added just below.

When this sqr runs, it throws the 'Syntax of the string representation
of a datetime value is incorrect' error. That tells me that 'AND
Z.ACCOUNTING_DT = $ACCOUNTING_DT' is obviously wrong, however I don't
know how to fix. This gets even more complicated because what this
sqr does is load data from a .csv file and when it gets to the field
where the date is, we set that to the variable $ACCOUNTING_DT. Oracle
has no problem understanding it, but DB2 does.

Any ideas out there on how I can resolve this?

FROM PS_BI_ACCT_ENTRY Z
WHERE Z.BUSINESS_UNIT = $BUSINESS_UNIT
AND Z.INVOICE = $INVOICE
AND Z.LINE_SEQ_NUM = $LINE_SEQ_NUM
!002237..AND Z.ACCOUNTING_DT = TO_DATE($ACCOUNTING_DT,'YYYY-MM-DD')
AND Z.ACCOUNTING_DT = $ACCOUNTING_DT !002237..
AND Z.ACCT_ENTRY_TYPE = 'RR'
AND Z.DISC_SUR_LVL = 0
AND Z.DISC_SUR_ID = ' '
AND Z.TAX_AUTHORITY_CD = ' '
END-SELECT
Any suggestions would be very much appreciated!

Thanks, Brenda

What does the data look like on the CSV file for date.

I checked the csv file and the date is 2003-09-25 which is the correct
format. Do you think it is because when it reads the .csv file I set
that date to a variable ($ACCOUNTING_DT) and it needs me to do
something else to that value when it reads it? I don't know what else
to look at. Any other ideas would be great.

Thanks, Brenda

[Mark A]

"Brenda" <bl*********@yahoo.com> wrote in message
news:19*************************@posting.google.co m...

I checked the csv file and the date is 2003-09-25 which is the correct
format. Do you think it is because when it reads the .csv file I set
that date to a variable ($ACCOUNTING_DT) and it needs me to do
something else to that value when it reads it? I don't know what else
to look at. Any other ideas would be great.

Thanks, Brenda

I believe the value needs to have single quotes around it like:
'2003-09-25'

[Brenda]

"Mark A" <[e-mail]> wrote in message news:<aG******************@news.uswest.net>...

"Brenda" <bl*********@yahoo.com> wrote in message
news:19*************************@posting.google.co m...

I checked the csv file and the date is 2003-09-25 which is the correct
format. Do you think it is because when it reads the .csv file I set
that date to a variable ($ACCOUNTING_DT) and it needs me to do
something else to that value when it reads it? I don't know what else
to look at. Any other ideas would be great.

Thanks, Brenda

I believe the value needs to have single quotes around it like:
'2003-09-25'

I contacted our DBA this morning and that was her solution also. We
tried changing the csv to '2003-09-25' for that field and it didn't
work--still getting the same error. Any other thoughts?

Thanks, Brenda