| Author |
Message
|
| chaitu |
 Posted: Thu Oct 29, 2015 1:39 am Post subject: Date time Format |
 |
|
Voyager
Joined: 15 Apr 2014
Posts: 89
|
Hi All,
I am facing an issue while casting a time stamp in format.
My input date time format is like "2015-07-04T05:05:00Z"
I need to store it into DB2. My code is like
DECLARE T TimeStamp;
SET T = Cast(InputRoot.XMLNSC.TIME AS TIMESTAMP FORMAT 'yyyy-MM-dd"T"HH:mm:ss Z)
I am getting a error like
Text:CHARACTER:Literal failed to match
Insert
Type:INTEGER:5
Text:CHARACTER:2015-07-04T08:55:00Z
Insert
Type:INTEGER:5
Text:CHARACTER:yyyy-MM-dd"T"HH:mm:ss Z
Insert
Type:INTEGER:5
Text:CHARACTER:T
Insert
Type:INTEGER:5
Text:CHARACTER:""
Could any one please help me how to achieve the above mentioned pattern.
Regards. |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Thu Oct 29, 2015 4:18 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
You're missing a single quote after the Z... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Thu Oct 29, 2015 4:24 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Replace the " (double Quotes) with TWO single Quotes
However this page
http://www-01.ibm.com/support/knowledgecenter/SSKM8N_8.0.0/com.ibm.etools.mft.doc/ak05616_.htm?lang=en
Should be bookmarked (V9 & V10 are similar)
The 'I' format may well meet your needs. Give it a try.
Failing that then this will be close to what you want
| Code: |
... TIMESTAMP format 'yyyy-MM-dd''T''HH:mm:ssZZZ'
|
_________________
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 |
|
|
| chaitu |
| Posted: Thu Oct 29, 2015 11:17 pm Post subject: |
|
|
Voyager
Joined: 15 Apr 2014
Posts: 89
|
Hi All,
Thanks for your Inputs.
My input date time format is like 2015-07-04T08:55:00Z
the code is CAST(input AS TIMESTAMP FORMAT 'yyyy-MM-dd''T''HH:mm:ssZZZ');
out put i am getting is 2015-07-04 14:25:00.000
why i am getting in different format and why not getting T even if i use I also same problem what else i need to change to get same like 2015-07-04T08:55:00Z.
Regards |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Oct 30, 2015 12:21 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
A timestamp object is a BINARY object.
The '2015-07-04 14:25:00.000' is a character representation of the binary data held in that object.
you used an input time in ZULU (UTC).
08:55 UTC has been converted into a TIMESTAMP that uses your local TIMEZONE
14:25 - 08:55 = 5:30 which leads me to think that you are in India.
If you want to keep all your times UTC/ZULU then use the GMTTIMESTAMP data format.
Go on, do some experimentation. cast a CHAR into both a TIMESTAMP and GMTTIMESTAPM variable and see what values you get.
That way you will learn all about this stuff. It takes some time to get your head around it all.
Actually is it easier for you with that large time difference. Here in the UK we often get confused because for 5 months a year
08:55 Zulu/UTC/GMT is actually the local time as well. Then for 7 months it is one hour out.
_________________
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 |
|
|
| timber |
| Posted: Fri Oct 30, 2015 2:01 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
Or, to put it another way, a TIMESTAMP object is a logical representation of an instant in time. It has a default lexical format as specified by the ESQL language reference.
You can convert a TIMESTAMP object to another lexical representations using CAST myTimeStamp AS CHARACTER pattern '<myPattern>'.
Having said all of that...why are you manually CASTing an input dateTime that is located in InputRoot.XMLNSC. If you have an xsd for your input message then you can ask the XMLNSC parser to create a richly-typed message tree for you ( see the 'Build tree using XML Schema' option on the input node ). |
|
| Back to top |
|
|
|
|
