| Author |
Message
|
| Dhiren |
 Posted: Tue Dec 20, 2005 5:44 pm Post subject: SUBSTRING |
 |
|
Novice
Joined: 27 Jan 2005
Posts: 17
|
Hi,
I have a CAST and a SUBSTRING funtion here ....
SET inDate=SUBSTRING(CAST (Header.Order.ReferenceDate AS CHAR) FROM 12 FOR 19);
If the source field ReferenceDate is of date type (yyyy-mm-dd hh:mi:ss). The above cast is definitely worng as from the 12th position there is no enough place for 19 length.
I tried this in a sample code and found out that field 'inDate' is assigned with the whole "ReferenceDate" (without substring).... does anybody know why it behaves like this ? shouldnt it tell me that the Substring function used here is wrong instead ? |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Dec 21, 2005 1:56 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
The answer should be a substring from 12 until the end of the source string (assuming the src is > 12 and < 19 in length).
Can you post your sample code, sample input data and results, along with the version and fix pack of the broker please.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Dec 21, 2005 9:34 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Well, it the field "Header.Order.ReferenceDate" is declared as a WBI Timestamp, then your code looks OK. Because when you CAST a Timestamp as CHAR, you get a preceeding literal of: TIMESTAMP.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| Dhiren |
| Posted: Wed Dec 21, 2005 4:40 pm Post subject: |
|
|
Novice
Joined: 27 Jan 2005
Posts: 17
|
Hi EddieA,
Looks like this one almosts solves my problem. Could you please tell me exactly what happens when u CAST a Timestamp as CHAR ?
Lets say I have a date comming in as yyyy-mm-dd hh:mi:ss , and if i cast this to char then what is the reult ? Like u said that u'l get a preceeding literal of: TIMESTAMP. Then will it be TIMESTAMP yyyy-mm-dd hh:mi:ss ?
Thanks for the reply guys..
Cheers |
|
| Back to top |
|
|
| JT |
| Posted: Wed Dec 21, 2005 5:16 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
In the future, there's no need to wait for a reply, you can discover the answer yourself by searching the Information Center: http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/topic/com.ibm.etools.mft.doc/ak05680_.htm?
| Quote: |
CAST TIMESTAMP to CHARACTER
The result is a string conforming to the definition of a TIMESTAMP literal, whose interpreted value is the same as the source timestamp value.
For example:
CAST(TIMESTAMP '2002-10-05 09:24:15' AS CHARACTER)
returns
TIMESTAMP '2002-10-05 09:24:15' |
|
|
| Back to top |
|
|
| EddieA |
| Posted: Wed Dec 21, 2005 5:18 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
| Quote: |
| Lets say I have a date comming in as yyyy-mm-dd hh:mi:ss , and if i cast this to char then what is the reult |
If it's "coming in" to the broker, then it's almost certainly already character, so the result will be: yyyy-mm-dd hh:mi:ss
You will only get the TIMESTAMP literal if you CAST a field that is DECLAREd as a TIMESTAMP.
| Quote: |
| Then will it be TIMESTAMP yyyy-mm-dd hh:mi:ss |
Maybe IBM introduced the Trace Node, or the Debugger for a reason. 
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
|
|
