| Author |
Message
|
| RB |
 Posted: Wed May 09, 2007 9:03 am Post subject: Datetime conversion |
 |
|
Acolyte
Joined: 23 May 2006
Posts: 56
|
Hi All,
I am trying the following code to cast the datetime.
| Code: |
| CAST( '2001-10-10T11:12:13-05:00' AS TIMESTAMP FORMAT 'yyyy-MM-ddTHH:mm:ssZZZ') |
But this gives me the follwing error.
RecoverableException BIP3204S: Input expression ''2001-10-10T11:12:13-05:00'' does not match FORMAT expression ''yyyy-MM-ddTHH:mm:ssZZZ''. Parsing failed to match ''T11:12:13-05:00'' with ''T''.
The given expression contains data which does not match the current element of the FORMAT expression
Can anyone please let me know what I am doing wrong here? I am using MB6 on Linux.
Regards,
RB |
|
| Back to top |
|
 |
| marcin.kasinski |
| Posted: Wed May 09, 2007 11:05 am Post subject: Re: Datetime conversion |
|
|
Sentinel
Joined: 21 Dec 2004
Posts: 850
Location: Poland / Warsaw
|
Is it TIMESTAMP ?
TIMESTAMP example is 2006-02-01 13:13:56.444730
I haven't test but can you try with :
| Code: |
| CAST( '2001-10-10 11:12:13' AS TIMESTAMP FORMAT 'yyyy-MM-ddTHH:mm:ssZZZ') |
_________________
Marcin |
|
| Back to top |
|
|
| RB |
| Posted: Wed May 09, 2007 11:27 am Post subject: |
|
|
Acolyte
Joined: 23 May 2006
Posts: 56
|
Thanks for your reply.
Yes it is timestamp. But I am getting this in this format from the input message with a T in between. I am extracting the timestamp from one of the input fields.
I think I am not using the right patter for casting. If I am try the following, it works fine.
| Code: |
| CAST( '2001-10-10 11:12:13-03:00' AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ssZZZ'); |
How can I specify 'T' in the pattern? Am I doing it wrong?
Regards,
RB |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed May 09, 2007 11:28 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Try using a pattern of 'I'.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| RB |
| Posted: Wed May 09, 2007 11:53 am Post subject: |
|
|
Acolyte
Joined: 23 May 2006
Posts: 56
|
Thanks jefflowrey. It worked with 'I' pattern.
Regards,
Rijesh |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 09, 2007 2:53 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
| RB wrote: |
Thanks for your reply.
Yes it is timestamp. But I am getting this in this format from the input message with a T in between. I am extracting the timestamp from one of the input fields.
I think I am not using the right patter for casting. If I am try the following, it works fine.
| Code: |
| CAST( '2001-10-10 11:12:13-03:00' AS TIMESTAMP FORMAT 'yyyy-MM-dd HH:mm:ssZZZ'); |
How can I specify 'T' in the pattern? Am I doing it wrong?
Regards,
RB |
I'd check to make sure I got the difference between dd and DD right.
One expects the date in julian format (days in the year) the other looks for days in the month...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| RB |
| Posted: Fri May 11, 2007 8:45 am Post subject: |
|
|
Acolyte
Joined: 23 May 2006
Posts: 56
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri May 11, 2007 1:22 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
Have you tried:
| Code: |
| CAST( '2001-10-10 11:12:13-03:00' AS TIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ssZZZ'); |
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
