| Author |
Message
|
| ANorm |
 Posted: Sat Jul 12, 2003 7:07 pm Post subject: Timestamp Format |
 |
|
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 |
|
 |
| kirani |
| Posted: Sat Jul 12, 2003 9:55 pm Post subject: |
|
|
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 |
|
|
| ANorm |
| Posted: Sun Jul 13, 2003 6:40 am Post subject: |
|
|
Newbie
Joined: 15 May 2003
Posts: 7
|
Could you please provide the ESQL?
Thank you. |
|
| Back to top |
|
|
| kirani |
| Posted: Sun Jul 13, 2003 9:10 am Post subject: |
|
|
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.
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 |
|
|
| ANorm |
| Posted: Sun Jul 13, 2003 11:16 am Post subject: |
|
|
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 |
|
|
| kirani |
| Posted: Mon Jul 14, 2003 3:19 pm Post subject: |
|
|
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 |
|
|
| Empeterson |
| Posted: Wed Jul 16, 2003 12:03 pm Post subject: |
|
|
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 |
|
|
| Craig B |
| Posted: Thu Jul 17, 2003 8:14 am Post subject: |
|
|
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 |
|
|
| Empeterson |
| Posted: Thu Jul 17, 2003 9:34 am Post subject: |
|
|
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 |
|
|
| kirani |
| Posted: Thu Jul 17, 2003 10:17 am Post subject: |
|
|
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 |
|
|
| Empeterson |
| Posted: Thu Jul 17, 2003 11:24 am Post subject: |
|
|
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 |
|
|
|
|
