| Author |
Message
|
| jorro004 |
 Posted: Thu Oct 25, 2007 6:26 am Post subject: Timestamp to Number Conversion |
 |
|
Acolyte
Joined: 25 Aug 2007
Posts: 50
|
Helo,
how can the following timestamp format can be converted to a number
Input : 2007-10-01 20:09:22.156 CDT
Output : 20071001200922156
I tried using CAST statement, but looks there is some problem which I'm not able to overcome
I have following code
SET OutputRoot.XMLNS.Message.id = CAST(Environment.id AS CHARACTER FORMAT 'yyyyMMddHHmmssSSS' )
I get the same date format as id
SET OutputRoot.XMLNS.Message.id = CAST(Environment.id AS TIMESTAMP FORMAT 'yyyyMMddHHmmssSSS' )
The following is the Error which I get
Error casting the value ''2007-10-01 20:09:22.156 CDT'' to 'TIMESTAMP'
Error casting character string '0-' to an integer.
How can I solve this?
Thanks |
|
| Back to top |
|
 |
| tleichen |
| Posted: Thu Oct 25, 2007 6:42 am Post subject: Re: Timestamp to Number Conversion |
|
|
Yatiri
Joined: 11 Apr 2005
Posts: 663
Location: Center of the USA
|
| jorro004 wrote: |
Helo,
how can the following timestamp format can be converted to a number
Input : 2007-10-01 20:09:22.156 CDT
Output : 20071001200922156 |
It looks like, mechanically, you already know what you want out of this.
| jorro004 wrote: |
I tried using CAST statement, but looks there is some problem which I'm not able to overcome
I have following code
SET OutputRoot.XMLNS.Message.id = CAST(Environment.id AS CHARACTER FORMAT 'yyyyMMddHHmmssSSS' )
I get the same date format as id
SET OutputRoot.XMLNS.Message.id = CAST(Environment.id AS TIMESTAMP FORMAT 'yyyyMMddHHmmssSSS' )
The following is the Error which I get
Error casting the value ''2007-10-01 20:09:22.156 CDT'' to 'TIMESTAMP'
Error casting character string '0-' to an integer.
How can I solve this?
Thanks |
You may need to learn more about programming basics; more specifically, the difference between integer and character internal representations. Then you would know that 20071001200922156 cannot be represented as an integer (at least not in a 32-bit architecture). Also, in several languages, there are already built-in functions that allow you to compare date values, etc., so no conversion is necessary to achieve those operations. 
_________________
IBM Certified MQSeries Specialist
IBM Certified MQSeries Developer |
|
| Back to top |
|
|
| jorro004 |
| Posted: Thu Oct 25, 2007 6:49 am Post subject: so no conversion is necessary to achieve those operations. |
|
|
Acolyte
Joined: 25 Aug 2007
Posts: 50
|
How can I change the format of a string from date to 17 digit number using ESQL
Can you please elaborate?
Thanks |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Oct 25, 2007 7:05 am Post subject: Re: so no conversion is necessary to achieve those operation |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| jorro004 wrote: |
How can I change the format of a string from date to 17 digit number using ESQL
|
You need to convert it into a purely digit representation (straight string manipulation) and then cast it as a numeric format big enough to hold it.
I'll take the word of tleichen that it's too big for an integer, I've not counted but it looks too big.
Another question is why bother? If it's being output as an Id field in an XML document why specifically cast it as an integer (is the tag specifically identiegied as an xs:integer type? If so, why? Why not xs:date?)
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jorro004 |
| Posted: Thu Oct 25, 2007 7:26 am Post subject: |
|
|
Acolyte
Joined: 25 Aug 2007
Posts: 50
|
I'm getting Input parameter as date field, which I convert it as a ID field to track message inside message broker
Probably, I should try using string manipulation funcitons as you suggested. As I thaught, CAST function can work
Thanks for your suggestions |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Oct 25, 2007 7:33 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| jorro004 wrote: |
I'm getting Input parameter as date field, which I convert it as a ID field to track message inside message broker
|
If it's input through an MQInput node, why not use the MsgId? Date/time is unreliable as an id; sooner or later two messages turn up at the same system time 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jorro004 |
| Posted: Thu Oct 25, 2007 7:42 am Post subject: |
|
|
Acolyte
Joined: 25 Aug 2007
Posts: 50
|
I'm getting Input as a JMS message, which I assign id with timestamp micro sec that makes unique.
Can you please explain how this can be done
"You need to convert it into a purely
digit representation (straight string manipulation) and then cast it as a
numeric format big enough to hold it"
how this can be acheived using ESQL like sample code or function name to do that.
thanks |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Oct 25, 2007 7:59 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| jorro004 wrote: |
| how this can be acheived using ESQL like sample code or function name to do that. |
This is standard programming, not rocket science. Look at the examples in the ESQL manual if you need a hint.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Oct 25, 2007 8:02 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
...
The problem is that the data being provided to the CAST function is not properly understood.
What datatype is Environment.id? What field of the input message was it populated as, what was done to it before the CAST?
The FORMAT clause is correct for outputting the format requested, at a rough glance.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jorro004 |
| Posted: Thu Oct 25, 2007 8:28 am Post subject: |
|
|
Acolyte
Joined: 25 Aug 2007
Posts: 50
|
What datatype is Environment.id? What field of the input message was it populated as, what was done to it before the CAST?
I'm just getting the Input Date field value and assiging that value to Environment.id and then using CAST statement
As below
Input Message
<Message>
<PutTime>2007-10-01 20:09:22.156 CDT</PutTime>
</Message>
Code
SET Environment.id = InputRoot.XMLNS.Message.PutTime;
SET OutputRoot.XMLNS.Message.id = CAST(Environment.id AS CHARACTER FORMAT 'yyyyMMddHHmmssSSS');
Thanks |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Oct 25, 2007 9:09 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Okay, so the field is *already* a Character value, because everything in XMLNS is character data.
So you need to CAST it as a TIMESTAMP first.
And on that cast, you need to specify a FORMAT clause that describes what the INPUT looks like.
Then you can cast it back to a character, and specify what the OUTPUT format should be.
Or you could just treat it as character data in the first place and use REPLACE, maybe, to remove anything that wasn't 0-9.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| jorro004 |
| Posted: Thu Oct 25, 2007 9:31 am Post subject: |
|
|
Acolyte
Joined: 25 Aug 2007
Posts: 50
|
that works!!!
I was doing CAST from CHARACTER to TIMESTAMP with Output format, which broker was not recognize. so the additional CAST helped in solving the problem
Thanks so much....for your help... |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Oct 25, 2007 9:39 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Also, the ESQL Integer data type, at least in v6 (which you have to be using, because you're using FORMAT), is MORE than big enough to handle this as a number.
| Quote: |
| The INTEGER data type holds an integer number in 64-bit two’s complement form. This gives a range of values between -9223372036854775808 and +9223372036854775807. |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
