MQSeries.net :: View topic - Getting a timestamp to adhere to ISO 8601 format...

Empeterson · Posted: Thu Feb 26, 2004 11:30 am Post subject:

ISO 8601 standard basically says that a date/time stamp should be in the follwing format:

YYYY-MM-DDTHH:MM:SS

The key here is the 'T" located before the time. The function CURRENT_TIMESTAMP returns:

YYYY-MM-DD HH:MM:SS

without the 'T'. I have tried a few different ways to construct that timestamp with the 'T' in it, but every time I either get an error or it doesnt come out quite right. I am starting to get very frustrated. I am sure there is something I am missing. I will outline below all the things I have tried. Please let me know if I have over looked anything.

1) First, I tried this:

SET OutputRoot.XML.Root.Timestamp = CURRENT_DATE || 'T' || CURRENT_TIME;

This wouldnt even deploy. I get this error in the log:

BIP2420E: (34, 103) : Invalid or incompatible data types for '||' operator.

2) Ok, so then I try this:

DECLARE myDate CHARACTER
DECLARE myTime CHARACTER

SET myDate = CURRENT_DATE;
SET myTime = CURRENT_TIME;

SET OutputRoot.XML.Root.Timestamp = myDate || 'T' || myTime;

This gives me:
<Root>
<Timestamp>DATE '2004-02-26'TTIME '13:28:12.462622'</Timestamp>
<Root>

which adds all that extra funky stuff in there.

3) Next I tried to declare my variables as date and time types:

DECLARE myDate DATE;
DECLARE myTime TIME;

SET myDate = CURRENT_DATE;
SET myTime = CURRENT_TIME;

SET OutputRoot.XML.Root.Timestamp = myDate || 'T' || myTime;

This is basically the same as 1), but interstingly enough, this deploys. It throws an exception though, the description being 'wrong type exception'.

4) Ok, so now I try and explicitly do the cast:

DECLARE myDate DATE;
DECLARE myTime TIME;

SET myDate = CURRENT_DATE;
SET myTime = CURRENT_TIME;

SET OutputRoot.XML.Root.Timestamp = CAST(myDate AS CHARACTER) || 'T' || CAST(myTime AS CHARACTER);

Not surprisingly, this returns the same result as 2).

5) My last attempt was to use EXTRACT to extract all the pieces I needed and rebuild them into the ISO format. The problem with EXTRACT is that it returns an integer, so if you grab a timestamp where any of the values are from 1-9, then you get a single digit rather then the 2 digit format that is required, ie 01,02 etc. Lets say its 9:02am. You run the follwing:

SET myTime = EXTRACT(HOUR FROM CURRENT_TIME) || EXTRACT(MINUTE FROM CURRENT_TIME);

You will get 92, not 0902 which would be the correct way to display that time. I could do a check on that value and if it is less than 10, I could throw a zero in front of it I guess, but that seems like a lot of work just to produce a timestamp. Maybe I am overlooking something simple and making this way too complicated, but it seems to me that the date/time functions could use some work. If anyone could offer any suggestions I would appreciate it. Thank you.

FYI: I am running WMQI 2.1, CSD4.
_________________
IBM Certified Specialist: MQSeries
IBM Certified Specalist: Websphere MQ Integrator