| Author |
Message
|
| ican.sbyn |
 Posted: Tue Aug 25, 2009 3:47 am Post subject: INSERTING 'DATE' into Oracle data base from ESQL |
 |
|
Novice
Joined: 14 Jul 2009
Posts: 19
|
Hi,
I have a requirement to insert a 'DATE' into the Oracle database. The input is comming 07152009 (ie 15th Jul 2009).
I have a Database table whose structure is like:
CREATE TABLE ABC1
(
ID_POP NUMBER(15),
LN_PIUL NUMBER,
VB_DAT DATE
)
I need to enter a data in this table. From the Oracle database,I can insert the data with the following insert scripts:
1) INSERT INTO ABC1 (ID_POP ,LN_PIUL,VB_DAT) VALUES (120,220,'15-JUL-2009');
2)INSERT INTO ABC1 (ID_POP ,LN_PIUL,VB_DAT) VALUES (120,220,TO_DATE( '07/15/2009','MM/DD/YYYY'));
BUT from ESQL , when I try to enter this, I am getting the following error:
Text:CHARACTER:[DataDirect][ODBC Oracle driver]Invalid character value. Error in parameter 3.
I am using the following code:
| Code: |
DECLARE vDateString,vMonth,vDate,vYear CHAR;
SET vDateString = refData.ns:UserDetail.*:SellDate;
-- So, the vDateString now contains 07152009
SET vMonth = SUBSTRING (vDateString FROM 1 FOR 2);
SET vDate = SUBSTRING (vDateString FROM 3 FOR 2);
SET vYear = SUBSTRING (vDateString FROM 5 FOR 4);
SET last_Sell_date = vDate||'/'||vMonth||'/'||vYear;
-- So, now, last_Sell_date is 15/07/2009
INSERT INTO Database.{Schema}.ABC1(ID_POP ,LN_PIUL,VB_DAT) VALUES (iDValue, SeqNo,last_Sell_date );
|
Can any body let me know, what I am missing, OR what should be the correct code (Format..)? |
|
| Back to top |
|
 |
| Bartez75 |
| Posted: Tue Aug 25, 2009 5:19 am Post subject: |
|
|
Voyager
Joined: 26 Oct 2006
Posts: 80
Location: Poland, Wroclaw
|
Just to be sure,
This doesn't work from ESQL?
| Code: |
INSERT INTO Database.{Schema}.ABC1(ID_POP ,LN_PIUL,VB_DAT) VALUES (120,220,'15-JUL-2009');
|
|
|
| Back to top |
|
|
| Luke |
| Posted: Tue Aug 25, 2009 5:22 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
When you insert the date with slashes directly to Oracle, you're using the Oracle function TO_DATE, but in your ESQL you just pass the date with slashes ... does it work if you replace the slashes with hyphens in your ESQL?
If not, you could use PASSTHRU and use TO_DATE, or continue to use ESQL INSERT and cast it to the relevant data type in Broker? Not sure what this is off the top of my head, maybe TIMESTAMP for Oracle DATE? |
|
| Back to top |
|
|
| ican.sbyn |
| Posted: Tue Aug 25, 2009 5:55 am Post subject: |
|
|
Novice
Joined: 14 Jul 2009
Posts: 19
|
INSERT INTO Database.{Schema}.ABC1(ID_POP ,LN_PIUL,VB_DAT) VALUES (120,220,'15-JUL-2009'); -- This will work.
I want the following to work:
| Code: |
SET vDateString = refData.ns:UserDetail.*:SellDate;
------ESQL CODE
------ESQL CODE
SET last_Sell_date = ---APPROPRIATE FORMAT
INSERT INTO Database.{Schema}.ABC1(ID_POP ,LN_PIUL,VB_DAT) VALUES (iDValue, SeqNo,last_Sell_date ); |
|
|
| Back to top |
|
|
| Luke |
| Posted: Tue Aug 25, 2009 5:59 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
| Quote: |
| SET last_Sell_date = vDate||'/'||vMonth||'/'||vYear; |
Spot the difference ... |
|
| Back to top |
|
|
| ican.sbyn |
| Posted: Tue Aug 25, 2009 6:15 am Post subject: |
|
|
Novice
Joined: 14 Jul 2009
Posts: 19
|
Well.....to do that , I have to create one more procedure to determine the month (The value of vMonth--whether its 'JUL'/'JAN' etc. Simple numeric value will be rejected!(As per the input string '07152009', the value of vMonth is 07). I am sure , this can be done with a proper date format  |
|
| Back to top |
|
|
| Luke |
| Posted: Tue Aug 25, 2009 6:28 am Post subject: |
|
|
Centurion
Joined: 10 Nov 2008
Posts: 128
Location: UK
|
You don't really need to write your own procedures for this type of thing, you can CAST between date/times and strings and vice-versa and specify a format.
See 'CAST function', and 'Formatting and parsing dateTimes as strings' in the information center.
If you have a date/time in string in one format, and want it in another, you can for example CAST to a TIMESTAMP specifying the input format, then CAST back to a string specifying the format you want.
Hope that helps ... |
|
| Back to top |
|
|
| napier |
| Posted: Tue Aug 25, 2009 10:59 am Post subject: |
|
|
Apprentice
Joined: 09 Oct 2007
Posts: 48
Location: USA
|
| Quote: |
INSERT INTO Database.{Schema}.ABC1(ID_POP ,LN_PIUL,VB_DAT) VALUES (iDValue,
SeqNo,
CAST(refData.ns:UserDetail.*:SellDate AS DATE FORMAT 'MMddyyyy'); |
I do use this statement quite a lot in my environment i never have any problem with the above statement. |
|
| Back to top |
|
|
| ican.sbyn |
| Posted: Tue Aug 25, 2009 9:51 pm Post subject: |
|
|
Novice
Joined: 14 Jul 2009
Posts: 19
|
Here is the solution:
| Code: |
DECLARE return_date DATE;
SET vDateString = refData.ns:UserDetail.*:SellDate;
-- So, the vDateString now contains 07152009
SET vMonth = SUBSTRING (vDateString FROM 1 FOR 2);
SET vDate = SUBSTRING (vDateString FROM 3 FOR 2);
SET vYear = SUBSTRING (vDateString FROM 5 FOR 4);
--SET last_Sell_date = vDate||'/'||vMonth||'/'||vYear;
-- Used '-' instead of '/'!!
SET last_Sell_date =vDate||'-'||vMonth||'-'||vYear;
SET return_date = CAST(last_Sell_date AS DATE FORMAT 'dd-MM-yyyy');
INSERT INTO Database.{Schema}.ABC1(ID_POP ,LN_PIUL,VB_DAT) VALUES (iDValue, SeqNo,return_date );
|
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Aug 26, 2009 4:50 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Your "solution" is doing a lot of unnecessary work.
A single CAST statement with an appropriate format should be all that is needed.
Or proper use of the XMLNSC parser and an XSD to cause the ESQL Message Tree to be populated with native data types rather than Character variables. |
|
| Back to top |
|
|
| napier |
| Posted: Wed Aug 26, 2009 1:49 pm Post subject: |
|
|
Apprentice
Joined: 09 Oct 2007
Posts: 48
Location: USA
|
| For a simple CAST function in one line you are doing all unnecessary substrings and casting |
|
| Back to top |
|
|
| WMBSAM |
| Posted: Fri Mar 01, 2013 3:14 pm Post subject: |
|
|
Voyager
Joined: 02 Oct 2009
Posts: 90
Location: Atlanta
|
ican.sbyn solution works when there is little help from casting functionality
how about casting 2/9/13 00:00 to 2013-03-01T11:37:39-04:00
??
any suggestion of a casting is going to work for this?? |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Mar 01, 2013 4:28 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
Is there any particular reason for reopening a 4-year old thread, rather than starting a new one?
| WMBSAM wrote: |
how about casting 2/9/13 00:00 to 2013-03-01T11:37:39-04:00
?? |
These are two different values, representing two different points in time, almost a month apart.
CAST is useful for converting from one datatype to another, but it's not meant to change the underlying value. |
|
| Back to top |
|
|
| WMBSAM |
| Posted: Fri Mar 01, 2013 5:36 pm Post subject: |
|
|
Voyager
Joined: 02 Oct 2009
Posts: 90
Location: Atlanta
|
| Quote: |
Is there any particular reason for reopening a 4-year old thread, rather than starting a new one? |
To avoid disconnect with the existing thread which i am refering to solve my existing problem
| Quote: |
how about casting 2/9/13 00:00 to 2013-03-01T11:37:39-04:00
?? |
my bad i mean casting 2/9/13 00:00 to 2013-02-09T11:37:39-04:00(mm/dd/yy hh:mm--> yyyy-mm-ddThh:mm:ss) |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Mar 01, 2013 10:03 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| WMBSAM wrote: |
| Quote: |
Is there any particular reason for reopening a 4-year old thread, rather than starting a new one? |
To avoid disconnect with the existing thread which i am refering to solve my existing problem
| Quote: |
how about casting 2/9/13 00:00 to 2013-03-01T11:37:39-04:00
?? |
my bad i mean casting 2/9/13 00:00 to 2013-02-09T11:37:39-04:00(mm/dd/yy hh:mm--> yyyy-mm-ddThh:mm:ss) |
Not quite right.
What you meant was from M/d/yy hh:mm to yyyy-MM-dd'T'HH:mm:ssZZZ
And for your information, how do you go from 00:00 to 11:37:39-04:00 ???
have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
