ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Timestamp Format

Post new topic  Reply to topic
 Timestamp Format « View previous topic :: View next topic » 
Author Message
ANorm
PostPosted: Sat Jul 12, 2003 7:07 pm    Post subject: Timestamp Format Reply with quote

Newbie

Joined: 15 May 2003
Posts: 7

In WMQI, using substring & timestamp I got yyyy-mm-dd hh:mm:ss.

I like to get timestamp in the following format:

mm/dd/yy hh:mm:ss am or pm

Thanks
Back to top
View user's profile Send private message
kirani
PostPosted: Sat Jul 12, 2003 9:55 pm    Post subject: Reply with quote

Jedi Knight

Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA

There is no formatting function in WMQI, you need to write ESQL code to reformat the timestamp.
_________________
Kiran


IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries

Back to top
View user's profile Send private message Visit poster's website
ANorm
PostPosted: Sun Jul 13, 2003 6:40 am    Post subject: Reply with quote

Newbie

Joined: 15 May 2003
Posts: 7

Could you please provide the ESQL?

Thank you.
Back to top
View user's profile Send private message
kirani
PostPosted: Sun Jul 13, 2003 9:10 am    Post subject: Reply with quote

Jedi Knight

Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA

TIMESTAMP function will return you data into following format,
Code:

TIMESTAMP 'CCYY-MM-DD HH:MM:SS.FFFFFF'

First, you need to CAST this data CHAR. Now use SUBSTRING function to extract data (YY, MM, DD, HH) and create a new string into desired format. i.e.
Code:

'MM/DD/YY HH:MM:SS


You need to write some simple logic to convert the time and append AM/PM to this string.
_________________
Kiran


IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries



Last edited by kirani on Sun Jul 13, 2003 9:41 pm; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
ANorm
PostPosted: Sun Jul 13, 2003 11:16 am    Post subject: Reply with quote

Newbie

Joined: 15 May 2003
Posts: 7

Great idea . I will use the SUBSTRING function to extract data and create a new string into desired format.

However, I would appreciate further help with the logic to append the am/pm .

I am gratifying that you and others are always around to help. Thank you.
Back to top
View user's profile Send private message
kirani
PostPosted: Mon Jul 14, 2003 3:19 pm    Post subject: Reply with quote

Jedi Knight

Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA

Here you go ..
Code:

DECLARE CTSTP CHAR CAST (TIMESTAMP AS CHAR);
DECLARE CCYY CHAR SUBSTRING(CTSTP FROM 12 FOR 4);
DECLARE MM CHAR SUBSTRING (CTSTP FROM 17 FOR 2);
DECLARE DD CHAR SUBSTRING (CTSTP FROM 20 FOR 2);
DECLARE HH CHAR SUBSTRING (CTSTP FROM 23 FOR 2);
DECLARE MN CHAR SUBSTRING (CTSTP FROM 26 FOR 2);
DECLARE SS CHAR SUBSTRING (CTSTP FROM 29 FOR 2);
DECLARE FF CHAR SUBSTRING (CTSTP FROM 32 FOR 6);
DECLARE CT CHAR;
DECLARE NEWHH INT;
DECLARE CNEWHH CHAR;

SET NEWHH = CAST (HH AS INT);

IF ( NEWHH > 12 ) THEN
  SET NEWHH = NEWHH - 12;
  SET CT = 'PM';
ELSEIF (NEWHH = 12 ) THEN
  SET CT = 'PM';
ELSEIF (NEWHH = 0 ) THEN
  SET CT = 'AM';
  SET NEWHH = 12;
ELSE
  SET CT = 'AM';
END IF;

IF ( NEWHH < 10 ) THEN
  SET CNEWHH = '0' || CAST(NEWHH AS CHAR);
ELSE
  SET CNEWHH = CAST (NEWHH AS CHAR);
END IF;

SET OutputRoot.XML.Data = MM || '/' || DD || '/' || SUBSTRING(CCYY FROM 3 FOR 2) || ' ' || CNEWHH || ':' || MN || ':' || SS || ' ' || CT;


will give you data into following format:
Code:

MM/DD/YY HH:MM:SS.FFFFFF AM/PM


You could also try doing some optimizations into this code.
_________________
Kiran


IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries

Back to top
View user's profile Send private message Visit poster's website
Empeterson
PostPosted: Wed Jul 16, 2003 12:03 pm    Post subject: Reply with quote

Centurion

Joined: 14 Apr 2003
Posts: 125
Location: Foxboro, MA

There is also an EXTRACT function you can use that will give you the portions of the date/time stamp you need. For example:

EXTRACT(YEAR FROM CURRENT_DATE) would return 2003.
EXTRACT(DAY FROM CURRENT_DATE) would return 16
EXTRACT(HOUR FROM CURRENT TIME) would return, for me anyway, 16.

You can use these values and put them in any date/time format you need it to be, be it YYYYMMDD or YYYY-MM-DD or YYYY/MM/DD etc etc.

You can also use the EXTRACT against any of the datetime functions: CURRENT_TIMESTAMP,CURRENT_GMDATE, CURRENT_GMTIME etc.

Just an fyi.
_________________
IBM Certified Specialist: MQSeries
IBM Certified Specalist: Websphere MQ Integrator
Back to top
View user's profile Send private message Send e-mail AIM Address
Craig B
PostPosted: Thu Jul 17, 2003 8:14 am    Post subject: Reply with quote

Partisan

Joined: 18 Jun 2003
Posts: 316
Location: UK

If you are going to use the EXTRACT function to help reformat your date/time, then be aware that the EXTRACT function returns an INTEGER value. Therefore, if you have a month that is a 06, then EXTRACT will return just 6. So when you re-use the extracted components you may need to add the prefixed 0 back on, for values that are less than 10.
_________________
Regards
Craig
Back to top
View user's profile Send private message
Empeterson
PostPosted: Thu Jul 17, 2003 9:34 am    Post subject: Reply with quote

Centurion

Joined: 14 Apr 2003
Posts: 125
Location: Foxboro, MA

Yes, I forgot about that. Craig is correct. Thank you for that reminder.
_________________
IBM Certified Specialist: MQSeries
IBM Certified Specalist: Websphere MQ Integrator
Back to top
View user's profile Send private message Send e-mail AIM Address
kirani
PostPosted: Thu Jul 17, 2003 10:17 am    Post subject: Reply with quote

Jedi Knight

Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA

That's why i didn't use EXTRACT function in my code!
I hope with future CSD releases we will get more ESQL functions to do this kind of tricks!
_________________
Kiran


IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries

Back to top
View user's profile Send private message Visit poster's website
Empeterson
PostPosted: Thu Jul 17, 2003 11:24 am    Post subject: Reply with quote

Centurion

Joined: 14 Apr 2003
Posts: 125
Location: Foxboro, MA

It effectively amounts to the same amount of code either way you slice it. It boils down to preference really. I agree though, there really should be built in funtionality to handle that.
_________________
IBM Certified Specialist: MQSeries
IBM Certified Specalist: Websphere MQ Integrator
Back to top
View user's profile Send private message Send e-mail AIM Address
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Timestamp Format
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.