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_ENTR Y 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($ACCOUN TING_DT,'YYYY-MM-DD')
AND Z.ACCOUNTING_DT = $ACCOUNTING_DT !002237..
AND Z.ACCT_ENTRY_TY PE = '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
6  27849 
"Brenda" <bl*********@ya hoo.com> wrote in message
news:19******** *************** **@posting.goog le.com... 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_ENTR Y 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($ACCOUN TING_DT,'YYYY-MM-DD')
AND Z.ACCOUNTING_DT = $ACCOUNTING_DT !002237..
AND Z.ACCT_ENTRY_TY PE = '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.
>> 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.
jo*******@north face.edu (--CELKO--) wrote in message news:<a2******* *************** ****@posting.go ogle.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
"Mark A" <ma@switchboard .net> wrote in message news:<PZ******* ********@news.u swest.net>... "Brenda" <bl*********@ya hoo.com> wrote in message
news:19******** *************** **@posting.goog le.com... 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_ENTR Y 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($ACCOUN TING_DT,'YYYY-MM-DD')
AND Z.ACCOUNTING_DT = $ACCOUNTING_DT !002237..
AND Z.ACCT_ENTRY_TY PE = '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
"Brenda" <bl*********@ya hoo.com> wrote in message
news:19******** *************** **@posting.goog le.com... 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'
"Mark A" <ma@switchboard .net> wrote in message news:<aG******* ***********@new s.uswest.net>.. . "Brenda" <bl*********@ya hoo.com> wrote in message
news:19******** *************** **@posting.goog le.com... 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Tatiana Zadiraka |
last post by:
Here is what I get from CLP commands
db2 => VALUES date('20-12-2003')
SQL0181N The string representation of a datetime value is out of
range.
SQLSTATE=22007
db2 => VALUES date('20.12.2003')
1
----------
|
by: sandip |
last post by:
Hi all,
I am trying to load data from a Sybase table with 'datetime' column
into a DB2 table with 'Date' type column? I used the following command
:
db2 "IMPORT FROM tabname.dat OF DEL MODIFIED BY COLDEL^ delprioritychar
TIMESTAMPFORMAT=\"MMM-DD-YYYY HH:MM:SS:UUUTT\" DATEFORMAT=\"MMM DD
YYYY\" REPLACE INTO tabname"
|
by: Bob Stearns |
last post by:
My client wants to disallow a birth date in the future. First I tried
the obvious:
ALTER TABLE IS3.ANIMALS
ADD CONSTRAINT b_d_f
CHECK (birth_date<=current_date)
It failed because special registers (current_date) are not allowed in
check conditions. Then I tried the trigger below:
|
by: Serge Rielau |
last post by:
I figure someone else may have use for this toy, so here goes:
Prereq: DB2 V8.1 FP4
CREATE PROCEDURE DELETE_MANY_ROWS
(tabschema VARCHAR(128),
tabname VARCHAR(128),
predicate VARCHAR(1000),
commitcount INTEGER)
BEGIN
DECLARE SQLCODE INTEGER;
|
by: satishrajana |
last post by:
Hi,
My SQL returns a NULL in a datefield if there is no date in that field.
If there is a NULL in this column, I want to replace it with spaces in
my SELECT statement when I am selecting these records. Can any of you
experts out there help me with a sample SQL.
| | |
by: klaus.schu |
last post by:
I just receive an error during my CLI ODBC insert statement:
INSERT INTO (.....) VALUES (?, ?, ?, TIMESTAMP( CAST(? AS
VARCHAR(10)),'00:00:00'), ?, ?, ?, ?)";
the ? in the CAST( ) Statement is represented by:
char *go03_btag="10.10.2006";
SQLCODE = -180, SQLSTATE = 22008
MESSAGE: SQL0180N The syntax
of the string representation of a datetime value is incorrect.
|
by: Dagmar Zahorska |
last post by:
Hi, Could you pls. advice me how to get rid of this error?
I query a table with date formats 'yyyy-mm-dd' originally.
In detail view of the table in query section I see the date format is 'dd.mm.yyyy'.
In the limit window Brio does accept neither 'yyyy-mm-dd' nor 'yyyy.mm.dd' = I'm getting the SQLSTAT: 22007 error message.
I tried to select a date from existing dates listed in the view 'Show values'.
I also tried to limit the query by...
|
by: Dobber |
last post by:
I am trying to construct an SQL statment and pass it to an i5. However I keep getting the above error message. At its simplest I am trying to convert an 8 0 numeric field in the format 20070817 to a date so that I can subtract a number of days. The statement consists of:
Cast((SUBSTR(CHAR(OHDATE),7,2)||'/'|| SUBSTR(CHAR(OHDATE),5,2) ||'/'|| SUBSTR(CHAR(OHDATE),3,2)) As date)
I have also tried:
Date(SUBSTR(CHAR(OHDATE),7,2)||'/'||...
|
by: Ty |
last post by:
Syntax Error-Help!!
My error "SQL0180N The syntax of the string representation of a
datetime value is incorrect. SQLSTATE=22007" .
I'm using yyyy-mm-dd. I can see the dates in a column on another
query as 2007-06-17 23:00:13.827584 which is yyyy-mm-dd time(i
guess).
My attempt:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
