| Author |
Message
|
| jonny |
 Posted: Thu Jul 03, 2003 1:39 am Post subject: noofdays - julian date |
 |
|
Acolyte
Joined: 03 Jul 2003
Posts: 57
|
Hi,
I am trying to use the sql function dayofyear, for a given date, for example noofdays('2003-02-10') will be 41.
How can I implement this function in mqsi? I have tried using passthru, but without any luck.
I am using mqsi 2.1 CSD04, and DB2
Regards
Jonny |
|
| Back to top |
|
 |
| shalabh1976 |
| Posted: Thu Jul 03, 2003 4:44 am Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
I am not sure if all sql functions are DB2 supported.
I looked up noofdays and it was not listed, so the passthru function was failing.
You can write your own function to do the same using other standard functions. |
|
| Back to top |
|
|
| jonny |
| Posted: Thu Jul 03, 2003 5:46 am Post subject: |
|
|
Acolyte
Joined: 03 Jul 2003
Posts: 57
|
Sorry, that was a typo, I meant dayofyear, not noodays - I have used that function outside MQSI and it's working fine.
For example, from the unix command line I entered the following:
db2 "select dayofyear(current date) from sysibm.sysdummy1"
and this was calculated to: 184
Regards
Jonny |
|
| Back to top |
|
|
| jonny |
| Posted: Mon Jul 07, 2003 1:43 am Post subject: |
|
|
Acolyte
Joined: 03 Jul 2003
Posts: 57
|
| Come one guys can anyone tell me how to implement dayofyear function in MQSI? |
|
| Back to top |
|
|
| inder |
| Posted: Mon Jul 07, 2003 10:04 am Post subject: |
|
|
Apprentice
Joined: 24 Mar 2003
Posts: 49
Location: USA
|
DECLARE NO_OF_DAYS INT;
SET Environment.Variables.RESULTS[] = PASSTHRU('SELECT DAYOFYEAR(CURRENT_DATE) AS NOOFDAYS from sysibm.sysdummy1 ');
NO_OF_DAYS = CAST(Environment.Variables.RESULTS[1].NOOFDAYS AS INTEGER);
Hope this helps.
Inder |
|
| Back to top |
|
|
| EddieA |
| Posted: Tue Jul 08, 2003 9:42 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Or if you don't want to go all the way out to a DB:
Use the YEAR from the date you have to construct a date of 1/1/yyyy. Subtract that from the date you have gioving the result as an INTERVAL of DAYS and then add 1.
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| timjohnarm |
| Posted: Mon Feb 06, 2006 1:23 pm Post subject: |
|
|
Apprentice
Joined: 28 Sep 2004
Posts: 40
Location: Melbourne Australia
|
A first cut of the suggested function
CREATE FUNCTION CharToJulianDate(DD CHARACTER, MM CHARACTER, CCYY CHARACTER) RETURNS CHARACTER
BEGIN
DECLARE CCYYMMDD DATE;
DECLARE CCYY0101 DATE;
DECLARE DaysThisYear INTEGER;
DECLARE JulianResult CHARACTER;
DECLARE Pos INTEGER;
SET CCYYMMDD = CAST((CCYY || '-' || MM || '-' || DD) AS DATE);
SET CCYY0101 = CAST((CCYY || '-01-01') AS DATE);
SET DaysThisYear = CAST((CCYYMMDD - CCYY0101) DAY AS INTEGER) + 1;
SET JulianResult = '000' || CAST(DaysThisYear AS CHARACTER);
SET Pos = LENGTH(JulianResult) - 2;
SET JulianResult = CCYY || SUBSTRING(JulianResult FROM Pos FOR 3);
RETURN JulianResult;
END; |
|
| Back to top |
|
|
| EddieA |
| Posted: Mon Feb 06, 2006 1:44 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Hey, maybe after nearly 2 1/2 years he's still struggling with this. Who knows. 
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| timjohnarm |
| Posted: Mon Feb 06, 2006 4:39 pm Post subject: |
|
|
Apprentice
Joined: 28 Sep 2004
Posts: 40
Location: Melbourne Australia
|
More a case of finding a home for a potentially useful function. The two posts I updated were in the list of hits I got when I went searching for "julian".
When reduced to writing ESQL I quite often come here for code snippets. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Feb 07, 2006 2:05 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
And in V6 you could try the new FORMAT clause on a CAST
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| timjohnarm |
| Posted: Wed Feb 15, 2006 3:45 pm Post subject: |
|
|
Apprentice
Joined: 28 Sep 2004
Posts: 40
Location: Melbourne Australia
|
Sounds good. But then I'd be waiting for Refresh Pack 2. Still that's probably less than 2.5 years away  |
|
| Back to top |
|
|
|
|
