|
View previous topic :: View next topic
|
| Author |
Message |
gragha
New User
Joined: 25 Jul 2006
Posts: 23
|
|
|
|
Hi,
I know there is the month option in DB2 which will return the integer part of the month like 1 (for January), 12 (for December).
But is there any way we can get the character value of the month like January, December.
Please let me know. |
|
| Back to top |
|
 |
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
gragha,
There is nothing directly in DB2 (That I Know of) that will give you the month spelled out, Try this bit of SQL below.
| Code: |
DECLARE GLOBAL TEMPORARY TABLE DATE_TBL
(DT CHAR(10)
)
;
INSERT INTO SESSION.DATE_TBL (DT) VALUES ('2007-01-01');
INSERT INTO SESSION.DATE_TBL (DT) VALUES ('1999-12-31');
INSERT INTO SESSION.DATE_TBL (DT) VALUES ('2007-05-26');
INSERT INTO SESSION.DATE_TBL (DT) VALUES ('2007-08-14');
INSERT INTO SESSION.DATE_TBL (DT) VALUES ('2008-02-29');
INSERT INTO SESSION.DATE_TBL (DT) VALUES ('2010-12-25');
--;
SELECT S.DT AS SOURCE_DATE,
RTRIM(
SUBSTR('JANUARY '
|| 'FEBRUARY '
|| 'MARCH '
|| 'APRIL '
|| 'MAY '
|| 'JUNE '
|| 'JULY '
|| 'AUGUST '
|| 'SEPTEMBER'
|| 'OCTOBER '
|| 'NOVEMBER '
|| 'DECEMBER ',
(((MONTH(S.DT) - 1) * 9) + 1),9))
|| ' ' || RTRIM(CAST(DAY(S.DT) AS CHAR(2)))
|| ', ' || CAST(YEAR(S.DT) AS CHAR(4))
AS FORMATTED_DATE
FROM SESSION.DATE_TBL S
|
Results
| Code: |
+----------------------------------+
| SOURCE_DATE | FORMATTED_DATE |
+----------------------------------+
1_| 2007-01-01 | JANUARY 1, 2007 |
2_| 1999-12-31 | DECEMBER 31, 1999 |
3_| 2007-05-26 | MAY 26, 2007 |
4_| 2007-08-14 | AUGUST 14, 2007 |
5_| 2008-02-29 | FEBRUARY 29, 2008 |
6_| 2010-12-25 | DECEMBER 25, 2010 |
+----------------------------------+
|
|
|
| Back to top |
|
|
gragha
New User
Joined: 25 Jul 2006
Posts: 23
|
|
|
|
| Thanks, I think this will work for the requirement that I have. |
|
| Back to top |
|
|
Garpen
New User
Joined: 03 Jan 2007
Posts: 14
Location: Sweden
|
|
|
|
Another way :
| Code: |
SELECT CURRENT DATE
, SUBSTR(DIGITS(YEAR(CURRENT DATE)),7,4) || '-' ||
CASE MONTH(CURRENT DATE)
WHEN 1 THEN 'JAN'
WHEN 2 THEN 'FEB'
WHEN 3 THEN 'MAR'
WHEN 4 THEN 'APR'
WHEN 5 THEN 'MAY'
WHEN 6 THEN 'JUN'
WHEN 7 THEN 'JUL'
WHEN 8 THEN 'AUG'
WHEN 9 THEN 'SEP'
WHEN 10 THEN 'OCT'
WHEN 11 THEN 'NOV'
WHEN 12 THEN 'DEC'
ELSE ' '
END || '-' ||
SUBSTR(DIGITS(DAY(CURRENT DATE)),9,2)
FROM SYSIBM.SYSDUMMY1
---------+---------+---------+---------+---------+-----
---------+---------+---------+---------+---------+-----
2007-02-20 2007-FEB-20 |
and this one just gives the name of the month :
| Code: |
SELECT CASE CHAR(MONTH(CURRENT DATE))
WHEN '1' THEN 'JANUARY'
WHEN '2' THEN 'FEBRUARY'
WHEN '3' THEN 'MARCH'
WHEN '4' THEN 'APRIL'
WHEN '5' THEN 'MAY'
WHEN '6' THEN 'JUNE'
WHEN '7' THEN 'JULY'
WHEN '8' THEN 'AUGUST'
WHEN '9' THEN 'SEPTEMBER'
WHEN '10' THEN 'OCTOBER'
WHEN '11' THEN 'NOVEMBER'
WHEN '12' THEN 'DECEMBER'
ELSE 'UNKNOWN'
END AS MONTH
FROM SYSIBM.SYSDUMMY1
; |
|
|
| Back to top |
|
|
gragha
New User
Joined: 25 Jul 2006
Posts: 23
|
|
|
|
| Thanks all. It works |
|
| Back to top |
|
|
|
|
