| Author |
Message
|
| firoj.badsa |
 Posted: Sun Jul 12, 2009 11:29 am Post subject: DATE issue |
 |
|
Centurion
Joined: 16 Feb 2007
Posts: 104
|
I have a input XML file which have a date format of YYYYMMDD. I want to insert date in database table in the same format.
<Message>
...........
...........
<date></date>
..........
..........
</Message>
INSERT Command fails while I am trying to use Message.date as value for the field. It seems i have to alter the date field value. Can anyone please help.
Regards,
Firoj |
|
| Back to top |
|
 |
| Vitor |
| Posted: Sun Jul 12, 2009 11:51 am Post subject: Re: DATE issue |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| firoj.badsa wrote: |
I want to insert date in database table in the same format.
...........
It seems i have to alter the date field value. Can anyone please help.
|
If you have to insert date into the database table in the same format then you need to alter the database column so it allows the insert.
Or you need to insert the date into the existing format you'll need to write some ESQL (or similar) to reformat the date.
Of course, if we knew why the INSERT failed because you'd posted the error there might be more suggestions.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Jul 12, 2009 12:19 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Or perhaps firoj.badsa has used XMLNS or XMLNSC without a schema, such that the XML data is not being turned into a date value at all, but remains the string of characters that makes up the contents of all XML fields. |
|
| Back to top |
|
|
| Vitor |
| Posted: Sun Jul 12, 2009 12:25 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| Or perhaps firoj.badsa has used XMLNS or XMLNSC without a schema, such that the XML data is not being turned into a date value at all, but remains the string of characters that makes up the contents of all XML fields. |
Indeed, though I would have hoped if it was as simple as a character string being presented to a database date field there would have been an implicit conversion. I suspect this issue is that the database expects "YYYY/MM/DD" or "DDMMYYYY" to convert.
Though of course you could get round all this using a schema as you suggest and presenting a date.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| firoj.badsa |
| Posted: Sun Jul 12, 2009 10:22 pm Post subject: |
|
|
Centurion
Joined: 16 Feb 2007
Posts: 104
|
Hi,
I am using MRM parser for the XML message. I am getting the below error message ...
[IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007
Thanks! |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Sun Jul 12, 2009 11:18 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| firoj.badsa wrote: |
Hi,
[IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007
|
How is the Database colum defined?
Can you give us the ESQL data type you are using for the Date and also the output from a user trace showing the actual value you art trying to insert.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| firoj.badsa |
| Posted: Mon Jul 13, 2009 12:15 am Post subject: |
|
|
Centurion
Joined: 16 Feb 2007
Posts: 104
|
database column was defined as datatype DATE;
SET CH_TargetDate = SUBSTRING ( TEMP FROM 5 FOR 2 ) ||'/'||SUBSTRING ( TEMP FROM 7 FOR 2 )||'/'||SUBSTRING ( TEMP FROM 1 FOR 4 );
In trace window i can see CH_TargetDate having value 02/02/2009.
Still the insert command fails.
Thanks |
|
| Back to top |
|
|
| vmcgloin |
| Posted: Mon Jul 13, 2009 12:46 am Post subject: |
|
|
Knight
Joined: 04 Apr 2002
Posts: 560
Location: Scotland
|
Hi,
You should check with your DBA what format is expected. Or check the db2cli.ini DateTimeStringFormat keyword. If it is not USA then it won't work with that format.
Alternatively, experiment from the command line - try inserting with yyyy-mm-dd as I guess that might be the most likely setting.
It does not seem like a message broker problem and the error message is quite helpful...however there might be a better way to cast your date to another format without concatenating substrings.
Cheers,
Vicky |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Jul 13, 2009 1:03 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
From a DB2 command line on Windows, I can use two formats to insert a date.
create table db2admin.mytest (ZZDATE DATE NOT NULL, ZZCHAR CHAR(30) NULL)
insert into db2admin.mytest (zzdate) values ('2009-03-03','Hello World 1')
insert into db2admin.mytest (zzdate) values ('03-03-2009','Hello World 2')
select * from db2admin.mytest
ZZDATE
-------------------------------------------------
03/03/2009 Hello World 1
03/03/2009 Hello World 2
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| firoj.badsa |
| Posted: Mon Jul 13, 2009 1:19 am Post subject: |
|
|
Centurion
Joined: 16 Feb 2007
Posts: 104
|
Yup.. From DB2 command line its working fine.. but i donno why this fails from compute node.
[IBM][CLI Driver][DB2/NT] SQL0180N The syntax of the string
representation of a datetime value is incorrect. SQLSTATE=22007 |
|
| Back to top |
|
|
| firoj.badsa |
| Posted: Mon Jul 13, 2009 1:39 am Post subject: |
|
|
Centurion
Joined: 16 Feb 2007
Posts: 104
|
Working fine now!
The column was created with wrong datatype! I was expecting a DATE datatype but DBA created with TIMESTAMP datatype.
Thanks all for your help.
Regards,
Firoj |
|
| Back to top |
|
|
|
|
