| Author |
Message
|
| arun4ruu |
 Posted: Fri Jul 13, 2012 6:53 am Post subject: RE: Timestamp in WMB |
 |
|
Newbie
Joined: 13 Jan 2012
Posts: 8
|
Hi
The input date format is 02-03-2008
The output date format is 02/03/2008
The output file is CSV format. When i am writing the date in CSV format it is writing as 2/3/2008 but it is expected as 02/03/2008.
Here is the ESQL code:
| Code: |
| SET OutputRoot.XMLNSC.ExpireDate = CAST(CAST(InputRoot.XMLNSC.ExpirationDate AS DATE FORMAT 'MM-dd-yyyy') AS CHARACTER FORMAT 'MM/dd/yyyy'); |
Any inputs are really appreciated
Last edited by arun4ruu on Fri Jul 13, 2012 7:36 am; edited 1 time in total |
|
| Back to top |
|
 |
| Vitor |
| Posted: Fri Jul 13, 2012 7:22 am Post subject: Re: RE: Timestamp in WMB |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| arun4ruu wrote: |
| Any inputs are really appreciated |
What does the user trace say is happening?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| arun4ruu |
| Posted: Fri Jul 13, 2012 7:32 am Post subject: |
|
|
Newbie
Joined: 13 Jan 2012
Posts: 8
|
|
| Back to top |
|
|
| yellowbee |
| Posted: Fri Jul 13, 2012 8:12 am Post subject: |
|
|
Acolyte
Joined: 14 Apr 2012
Posts: 58
|
how about
| Quote: |
| SET OutputRoot.XMLNSC.ExpireDate = CAST(CAST(InputRoot.XMLNSC.ExpirationDate AS DATE FORMAT 'MM-dd-yyyy') AS CHARACTER FORMAT 'MM,dd,yyyy'); |
|
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 13, 2012 8:24 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| arun4ruu wrote: |
| It is 02/03/2008 |
So if the user trace says the result of the CAST is 02/03/2008 (which is what I'm assuming you mean by this cryptic & abbreviated comment) but it's being output as 2/3/2008 then surely that's a clue?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 13, 2012 8:25 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| yellowbee wrote: |
how about
| Quote: |
| SET OutputRoot.XMLNSC.ExpireDate = CAST(CAST(InputRoot.XMLNSC.ExpirationDate AS DATE FORMAT 'MM-dd-yyyy') AS CHARACTER FORMAT 'MM,dd,yyyy'); |
|
How about it? Given the OP wants / between the date items, how does replacing it with commas help? It's more likely to confuse any downstream process if a comma delimited date is written to a comma delimited file...... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Fri Jul 13, 2012 9:06 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi arun4ruu,
I don't think the issue is to do with WMB...it is writing the format in MM/dd/yyyy.
If you create a new csv file in Microsoft excel, type 02/03/2008 and click enter, it'll turn to 2/3/2008.
If you want to see it correctly, change the cell format to custom and set the format as mm/dd/yyyy...
Regards. |
|
| Back to top |
|
|
| arun4ruu |
| Posted: Fri Jul 13, 2012 10:53 am Post subject: |
|
|
Newbie
Joined: 13 Jan 2012
Posts: 8
|
Hi elvis_gn,
Thank you for your i/p. I have already changed the cell format as mm/dd/yyyy.
Looking that is there any possiblity other than the cell formatting. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 13, 2012 11:21 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| arun4ruu wrote: |
| Looking that is there any possiblity other than the cell formatting. |
So, to be clear, if you look in the user trace (not the debugger) you see the result of the CAST statement to be 02/03/2008 as expected.
If you look at the output file contents with Notepad you see 2/3/2008.
Am I correct in this?
_________________
Honesty is the best policy.
Insanity is the best defence.
Last edited by Vitor on Fri Jul 13, 2012 11:22 am; edited 1 time in total |
|
| Back to top |
|
|
| kimbert |
| Posted: Fri Jul 13, 2012 11:22 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| The output file is CSV format. When i am writing the date in CSV format it is writing as 2/3/2008 but it is expected as 02/03/2008. |
I have an important question for you. If your output format is CSV then why are you creating your output message tree under OutputRoot.XMLNSC?
If you explain your problem more clearly then you will probably get some useful answers. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jul 13, 2012 11:23 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kimbert wrote: |
| Quote: |
| The output file is CSV format. When i am writing the date in CSV format it is writing as 2/3/2008 but it is expected as 02/03/2008. |
I have an important question for you. If your output format is CSV then why are you creating your output message tree under OutputRoot.XMLNSC? |
Now that's a seriously good catch! 
It's so easy to miss the obvious..... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| arun4ruu |
| Posted: Sun Jul 15, 2012 9:30 am Post subject: |
|
|
Newbie
Joined: 13 Jan 2012
Posts: 8
|
Hello Kimbert,
The output message tree is
| Code: |
| OutputRoot.MRM.ExpireDate = CAST(CAST(InputRoot.XMLNSC.ExpirationDate AS DATE FORMAT 'MM-dd-yyyy') AS CHARACTER FORMAT 'MM,dd,yyyy'); |
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Sun Jul 15, 2012 9:37 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You should not be putting formatted data into an MRM field.
If the MRM field is of TIMESTAMP or datetime type, which it should be, then MRM itself should properly format it on output.
If the MRM field is of CHARACTER type, then you should change it to be of timestamp or datetime type, and set the physical properties to ensure that the physical representation is always correct regardless of the logical contents of the data.
That is the WHOLE POINT of a message set - to ISOLATE your Application from the PHYSICAL FORMAT. |
|
| Back to top |
|
|
|
|
