| Author |
Message
|
| Sai K |
 Posted: Tue Aug 02, 2011 6:40 am Post subject: |
 |
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
READ_TIMEFINAL = 2011-07-29T17:39:00;
insert into Database.SAMPLE VALUES(READ_TIMEFINAL);
When this insert operation performed from broker, I am seeing the result in
sql developer. it is showing as 29-JUL-11.
After insert, I am selecting the value and taking into environment variables..
set Environment.Variables.Result[] = select t.sampledt from Database.sample as t;
This is the value in environment variables: sampledt 2011-07-29 17:39:00.000
My expected output is: 29-JUL-11 17:39:00 |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Aug 02, 2011 6:52 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Sai K wrote: |
I am seeing the result in
sql developer. it is showing as 29-JUL-11. |
So SQL Developer is truncating the time and reformating the date when it displays it. And?
| Sai K wrote: |
This is the value in environment variables: sampledt 2011-07-29 17:39:00.000
My expected output is: 29-JUL-11 17:39:00 |
The value in the environment variable is a timestamp inherited from the select statement and almost certainly how the database is holding it internally (because if SQL Developer was not modifying the display, the time would disappear when you selected it back). This timestamp value is exactly the same as your expected result except:
- you want the date presented differently
- you don't want milliseconds
So format the date how you want is in the resulting character. The value doesn't change.
You need to get to grips with the difference between a date/time value, and it's representation.
_________________
Honesty is the best policy.
Insanity is the best defence.
Last edited by Vitor on Tue Aug 02, 2011 6:54 am; edited 1 time in total |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Aug 02, 2011 6:54 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Sai K wrote: |
sampledt 2011-07-29 17:39:00.000
My expected output is: 29-JUL-11 17:39:00 |
So. the next step is?
{hint, a simple bit of ESQL}
_________________
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 |
|
|
| Sai K |
| Posted: Tue Aug 02, 2011 10:52 am Post subject: |
|
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
Below is my code:
DECLARE READ_TIMEFINAL CHAR;
SET READ_TIMEFINAL = InputRoot.XMLNSC.GetLatestReadingByMeterNoResponse.readingDate; --SampleValue:2011-07-29T17:39:00
CALL UpdateDateTime(READ_TIMEFINAL);
insert into Database.SAMPLE VALUES(READ_TIMEFINAL);
Procedure:
CREATE PROCEDURE UpdateDateTime(INOUT READ_TIMEFINAL CHAR)
BEGIN
DECLARE ReadYY CHARACTER SUBSTRING(READ_TIMEFINAL FROM 3 FOR 2);
DECLARE ReadDD CHARACTER SUBSTRING(READ_TIMEFINAL FROM 9 FOR 2);
DECLARE ReadMM CHARACTER SUBSTRING(READ_TIMEFINAL FROM 6 FOR 2);
IF ReadMM = '01'THEN
SET ReadMM = 'JAN';
ELSEIF ReadMM ='02'THEN
SET ReadMM = 'FEB';
ELSEIF ReadMM ='03'THEN
SET ReadMM = 'MAR';
ELSEIF ReadMM ='04'THEN
SET ReadMM = 'APR';
ELSEIF ReadMM ='05'THEN
SET ReadMM = 'MAY';
ELSEIF ReadMM ='06'THEN
SET ReadMM = 'JUN';
ELSEIF ReadMM ='07'THEN
SET ReadMM = 'JUL';
ELSEIF ReadMM ='08'THEN
SET ReadMM = 'AUG';
ELSEIF ReadMM ='09'THEN
SET ReadMM = 'SEP';
ELSEIF ReadMM ='10'THEN
SET ReadMM = 'OCT';
ELSEIF ReadMM ='11'THEN
SET ReadMM = 'NOV';
ELSEIF ReadMM ='12'THEN
SET ReadMM = 'DEC';
ELSE
SET ReadMM = 'MON';
END IF;
DECLARE TimeValue CHARACTER SUBSTRING(READ_TIMEFINAL FROM 12 FOR  ;
SET READ_TIMEFINAL = ReadDD||'-'||ReadMM||'-'||ReadYY||' '||TimeValue;
END;
I am getting the exception as below:
Insert Type:INTEGER:5 Text:CHARACTER:[IBM][ODBC Oracle Wire Protocol driver]Invalid character value. Error in parameter 1.
Database column datatype is : DATE |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Aug 02, 2011 11:04 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Aug 02, 2011 11:05 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
There are 2 points here:
1) You've written a bunch of ESQL to format a date and in doing so re-invented a wheel which is much, much less round than the function IBM have kindly provided in ESQL for you;
2) As I and others have been saying throughout this post, there is a difference between a timestamp value as held in the database column and it's displayed representation. Now the software is saying it:
| Quote: |
| Invalid character value. Error in parameter 1 |
is database-ese for "You can't use that here".
Read what we're telling you and think!
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Aug 02, 2011 11:08 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
1 Code Tags! (Sigh, bangs head against a nearby wall)
2 Suddenly we have gone from a DB read to a DB insert....?????
3 Ever heard of CAST with a FORMAT?
4 Did you try other formats for the DB Insert Date Value?
_________________
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 |
|
|
| Vitor |
| Posted: Tue Aug 02, 2011 11:17 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| smdavies99 wrote: |
| 1 Code Tags! (Sigh, bangs head against a nearby wall) |
In this instance, they wouldn't have made much difference; the code's still contact admin.
| smdavies99 wrote: |
| 2 Suddenly we have gone from a DB read to a DB insert....????? |
I noticed this too but didn't want to confuse the situation further
| smdavies99 wrote: |
| 3 Ever heard of CAST with a FORMAT? |
Clearly not. There's never anyone to post a link when you need one.... 
| smdavies99 wrote: |
| 4 Did you try other formats for the DB Insert Date Value? |
I don't think the OP has really grasped the concept of data formats in any realistic sense. There seems to be a pervasive belief that '29-JUL-11' is a date not a string.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Sai K |
| Posted: Fri Aug 05, 2011 8:36 am Post subject: |
|
|
Novice
Joined: 23 Jun 2011
Posts: 22
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Aug 05, 2011 7:54 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Call me confused... It seems to me that people (aka developers) are abusing fields again. A date field should contain nothing else but a date.
It it needs also to contain some time value it is called a date/time stamp field for a reason.
AFAIK Oracle has both and subverting a date field for a date/time stamp is a no no in my book...   
Tell your developers to review their design 
_________________
MQ & Broker admin
Last edited by fjb_saper on Sat Aug 06, 2011 3:06 pm; edited 1 time in total |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Aug 06, 2011 11:05 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| fjb_saper wrote: |
| Call me confused... It seems to me that people (aka developers) are abusing fields again. A date field should contain nothing else but a date. |
Would the developers in this case be the Oracle developers?
The Oracle DATE type includes both the date (y,m,d) and the time (h,m,s).
The Oracle TIMESTAMP types add fractional seconds and an optional timezone. |
|
| Back to top |
|
|
|
|
