| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Date conversion |
« View previous topic :: View next topic » |
| Author |
Message
|
| jeasterl |
 Posted: Thu Aug 01, 2002 12:29 pm Post subject: Date conversion |
 |
|
Acolyte
Joined: 24 Jun 2001
Posts: 65
|
Hello all,
I am wondering if anyone has run into this problem. I have an application that sends the date field in this format:
MM/DD/YYYY HH:MM:SS
However, this date field is inserted into a Oracle table and Oracle will not accept a date in that format. Oracle will only accept it in this format:
DD-MMM-YY (i.e 04-JUL-02)
As I began my peliminary seach through the ESQL manual, I found that the month issue is easily solved with a CASE statement:
CASE Month
WHEN '01' THEN 'Jan'
WHEN '02' THEN 'Feb'
(and so on....)
I am a little lost on how I will remove the time and replace the '/' with '-' and will I "Copy Message Headers' or 'Copy Entire Message'.
Thanks! |
|
| Back to top |
|
 |
| sgopal |
| Posted: Thu Aug 01, 2002 12:41 pm Post subject: |
|
|
Acolyte
Joined: 30 Jul 2002
Posts: 63
|
jeasterl,
You can do a substring of the date field (MM/DD/YYYY HH:MM:SS) to get the month, day and year.
Convert MM to MMM as per your ESQL.
And form a string by concatenating as follows,
DD-field || '-' || MMM-field || '-' || YY field
Use the Copy Entire Message option if you need the message to be propagated ahead. |
|
| Back to top |
|
|
| Miriam Kaestner |
| Posted: Fri Aug 02, 2002 1:41 am Post subject: |
|
|
Centurion
Joined: 26 Jun 2001
Posts: 103
Location: IBM IT Education Services, Germany
|
| If you are working with MRM messages, then you don't need to code the date conversion in ESQL. Just specify the custom date format as a string for the DATETIME element, e.g. 'DD-MMM-YY' in your case. |
|
| Back to top |
|
|
| mpuetz |
| Posted: Mon Aug 12, 2002 12:41 pm Post subject: |
|
|
Centurion
Joined: 05 Jul 2001
Posts: 149
Location: IBM/Central WebSphere Services
|
Hi,
if you work with Oracle there is another, maybe simpler solution:
Let Oracle do the conversion for you with PASSTHRU
PASSTHRU('INSERT (date) INTO mytable VALUES(to_date(?."oracle_format_string"))',InputBody.XML.blabla.mydate);
Instead of
INSERT ("date") INTO mytable VALUES (InputBody.XML.blabla.mydate);
where oracle_format_string describes the format of your date-string.
Simply look-up the to_date SQL Function in the Oracle documentation
_________________
Mathias Puetz
IBM/Central WebSphere Services
WebSphere Business Integration Specialist |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
