MQSeries.net :: View topic - Passthru and DayOfWeek

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Passthru and DayOfWeek

Passthru and DayOfWeek

« View previous topic :: View next topic »

Author

Message

JohnG

Posted: Tue Jan 21, 2003 5:32 am Post subject: Passthru and DayOfWeek

Newbie

Joined: 21 Jan 2003
Posts: 7

Hi

I am trying to get the current day of week in a message flow. I am using the backend DB2 database to achieve this. Howver the follloiwng code returns an empty result set but works in DB2 CLP.

DECLARE TCNT INTEGER ;
DECLARE i INTEGER ;
DECLARE dayno INTEGER;
SET i = 1 ;
DECLARE sql CHAR ;
DECLARE today CHAR ;
SET today = CAST(current_date AS CHAR) ;
SET today = TRIM(SUBSTRING( today FROM 5)) ;

SET sql = 'SELECT dayofweek (' || today || ') from form_emails' ;
SET OutputDestinationList.Variables.TempData[] = passthru(sql);
SET dayno = CAST(OutputDestinationList.Variables.TempData[1] AS INTEGER);

Thanks in advance

John

yaakovd

Posted: Tue Jan 21, 2003 6:28 am Post subject:

Partisan

Joined: 20 Jan 2003
Posts: 319
Location: Israel

I don't know which format you have in DB, but

SET today = SUBSTRING(CAST(EXTRACT(YEAR FROM CURRENT_DATE) AS CHARACTER) FROM 3) ||
SUBSTRING((CAST(100 + EXTRACT(MONTH FROM CURRENT_DATE)AS CHARACTER)) FROM 2 FOR 3) ||
SUBSTRING((CAST(100 + EXTRACT(DAY FROM CURRENT_DATE)AS CHARACTER)) FROM 2 FOR 3);

will return YYMMDD format for today 030121
_________________
Best regards.
Yaakov
SWG, IBM Commerce, Israel

JohnG

Posted: Tue Jan 21, 2003 8:54 am Post subject:

Newbie

Joined: 21 Jan 2003
Posts: 7

Yaakov

Thanks for that. I really wanted the day of week e.g. Tuesday is a 3. But your code prompted me to use the date difference function mod 7 and add 4 for 1/1/03 - this was a Wednesday.

This now works. So thanks for help.

John

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Passthru and DayOfWeek

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