| Author |
Message
|
| dmw1986 |
 Posted: Mon Nov 17, 2008 11:49 am Post subject: Date Casts |
 |
|
Apprentice
Joined: 24 Sep 2008
Posts: 35
|
| Is there a way using the mapping node to strip off dashes in a date. We are being sent 9999-12-31 but our COBOL include and database only accepts 99991231. Thanks |
|
| Back to top |
|
 |
| sridhsri |
| Posted: Mon Nov 17, 2008 1:01 pm Post subject: |
|
|
Master
Joined: 19 Jun 2008
Posts: 297
|
I would recommend using MRM to convert between date formats and not using Mapping functions for this.
But if you had to use them, there are getyear, getmonth etc function and you'd have to use string concat function to create a date in the format you need. I strongly recommend using MRM for this though. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Nov 17, 2008 1:28 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| sridhsri wrote: |
I would recommend using MRM to convert between date formats and not using Mapping functions for this.
But if you had to use them, there are getyear, getmonth etc function and you'd have to use string concat function to create a date in the format you need. I strongly recommend using MRM for this though. |
Since version 6 you have a cast with format for date, time(?) and datestamp type values. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| dmw1986 |
| Posted: Mon Nov 17, 2008 1:43 pm Post subject: |
|
|
Apprentice
Joined: 24 Sep 2008
Posts: 35
|
I'll try to be a little more specific, I may have confused you. 
The message is coming in XML form such as
<nc:PersonBirthDate>
<nc:Date>1966-01-23</nc:Date>
</nc:PersonBirthDate>
My COBOL Include is only 8 long so I just need to strip the dashes to make it 19660123.
Can I do that in the mapping node?
Thanks for your rersponses, much appreciated. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Nov 17, 2008 2:11 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
You can easily do it in ESQL using a CAST to a FORMAT.
Lookup the syntax in the WBI Help and you will see what I mean.
Otherwise, set the output form in the output Message set to be YYYYMMDD
_________________
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 |
|
|
| kimbert |
| Posted: Mon Nov 17, 2008 2:28 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
My COBOL Include is only 8 long so I just need to strip the dashes to make it 19660123.
Can I do that in the mapping node? |
It probably is possible. But I agree with sridhsri - this is what the MRM parser is provided for. If you are not using MRM, why not? |
|
| Back to top |
|
|
| dmw1986 |
| Posted: Tue Nov 18, 2008 10:28 am Post subject: |
|
|
Apprentice
Joined: 24 Sep 2008
Posts: 35
|
I took up your ideas and am using MRM now. However for some reason the ESQL doesnt like my syntax. Can you help me? I've tried both YYYY and CCYY within my format but it says "CCYYMMDD" Cannot be Resolved.
| Code: |
| SET OutputRoot.MRM.CJ37CI80_INPUT_DATA.CJ37CI80_DOB = CAST(InputRoot.XMLNSC.mo_warrant:Warrant.mo_warrant:Person.nc:PersonBirthDate.nc:Date AS DATE FORMAT "CCYYMMDD"); |
|
|
| Back to top |
|
|
| dmw1986 |
| Posted: Tue Nov 18, 2008 10:47 am Post subject: |
|
|
Apprentice
Joined: 24 Sep 2008
Posts: 35
|
Edit, Nevermind still not working. Was able to deploy but still not stripping dashes.
SET OutputRoot.MRM.CJ37CI80_INPUT_DATA.CJ37CI80_DOB = CAST(InputRoot.XMLNSC.mo_warrant:Warrant.mo_warrant:Person.nc:PersonBirthDate.nc:Date AS CHARACTER FORMAT 'yyyyMMdd'); |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Nov 18, 2008 2:05 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
hi dmw1986,
| Quote: |
| Was able to deploy but still not stripping dashes |
You need to set the date format used by this field. You can do that in either of two places:
-at the message set level, where it will apply to all date and/or time fields in your message
- on the CWF properties of the field itself
re: this
| Code: |
| CAST(InputRoot.XMLNSC.mo_warrant:Warrant.mo_warrant:Person.nc:PersonBirthDate.nc:Date AS CHARACTER FORMAT 'yyyyMMdd') |
You should not need to convert a string to a date in your ESQL. The parser can do that for you, as detailed below:
If necessary ( i.e. if the input logical structure is different from the output logical structure ) create another message definition to describe the input message.
If you are on v6.1:
- XMLNSC can create all the data types in the message tree.
- you must specify the message set in the input node properties
- you must set validation to 'Content and Value'
- you must enable the Parser Property 'Build tree using XML Schema'
If on v6.0 or earlier:
- MRM can create all the data types in the message tree.
- create an XML physical format in your existing message set.
- remember to specify the correct Message Type in the input node properties
- remember to set the Message Format to 'XML1' or whatever name you chose for your physical format |
|
| Back to top |
|
|
| broker_new |
| Posted: Tue Nov 18, 2008 5:02 pm Post subject: |
|
|
Yatiri
Joined: 30 Nov 2006
Posts: 614
Location: Washington DC
|
I agree with sridhsri to use MRM parser for time,date related castings.
I faced one problem when casting timestamp.
Broker(6.0.3) was not considering the DST when using XMLNS,XMLNSC where MRM fixed it.
_________________
IBM ->Let's build a smarter planet |
|
| Back to top |
|
|
|
|
