|
View previous topic :: View next topic
|
| Author |
Message |
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
| Suppose i am having a date field in a table as 01/29/2006.If i want to display that field as JAN 29 ie only the month name and the date how can we do that with the help of sql query. |
|
| Back to top |
|
 |
Prajesh_v_p
Active User
Joined: 24 May 2006
Posts: 133
Location: India
|
|
|
|
Hi Prasanth,
I cannot find any built in fuction here...But u can very well use the case statments here..like
SELECT CASE MONTH('01/29/2006') WHEN 1 THEN SUBSTR('JAN',1,3)
WHEN 2 THEN SUBSTR('FEB',1,3)
.............
END CASE ,
SUBSTR('01/29/2006',4,2)
thanks,
Prajesh |
|
| Back to top |
|
|
mahsug
New User
Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ
|
|
|
|
Hi ,
Continuing to Prashanth , you can even think of writing a function or Stored Procedure, which you can think of reusing at various junctures.
Mahesh |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
You mean like this?
| Code: |
SELECT CURRENT_DATE AS CURRENT_DATE,
SUBSTR('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',
(((MONTH(CURRENT_DATE) - 1) * 3) + 1),3)
|| ' ' || CAST(DAY(CURRENT_DATE) AS CHAR(2))
AS FORMATTED_DATE
FROM SYSIBM.SYSDUMMY1
;
+-------------------------------+
| CURRENT_DATE | FORMATTED_DATE |
+-------------------------------+
| 2006-07-28 | JUL 28 |
+-------------------------------+
|
Dave |
|
| Back to top |
|
|
ravi17s
Warnings : 1
New User
Joined: 15 Aug 2003
Posts: 57
|
|
|
|
| Thats Cool david.Excelent Logic!!! |
|
| Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
Yes thats an excellent logic David
Thanks |
|
| Back to top |
|
|
antonrino.b
New User
Joined: 10 Jan 2006
Posts: 76
Location: Germany
|
|
|
|
That is an wonderful logic..
When executing the following query,
| Code: |
SELECT CURRENT_DATE AS CURRENT_DATE,
SUBSTR('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',
MONTH(CURRENT_DATE)*3-2) AS FORMATTED_DATE2
FROM SYSIBM.SYSDUMMY1; |
The result was,
| Code: |
+-----------------------------------------------------+
! CURRENT_DATE ! FORMATTED_DATE2 !
+-----------------------------------------------------+
! 03.08.2006 ! AUGSEPOCTNOVDEC !
+-----------------------------------------------------+ |
And when I executed the following query,
| Code: |
SELECT CURRENT_DATE AS CURRENT_DATE,
SUBSTR('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',
MONTH(CURRENT_DATE)*3-2,3) AS FORMATTED_DATE2
FROM SYSIBM.SYSDUMMY1; |
I got the following error,
| Code: |
SQLERROR ON SELECT COMMAND, PREPARE FUNCTION
RESULT OF SQL STATEMENT:
DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2
DSNT418I SQLSTATE = 42815 SQLSTATE RETURN CODE |
Then I modified the above query as below:
| Code: |
SELECT CURRENT_DATE AS CURRENT_DATE,
SUBSTR('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',
INT(MONTH(CURRENT_DATE)*3-2),INT(3)) AS FORMATTED_DATE2
FROM SYSIBM.SYSDUMMY1; |
I got the following result;
| Code: |
+-----------------------------------------------------+
! CURRENT_DATE ! FORMATTED_DATE2 !
+-----------------------------------------------------+
! 03.08.2006 ! AUG !
+-----------------------------------------------------+ |
Why is this so? What is wrong with the second query? |
|
| Back to top |
|
|
ranjanp
New User
Joined: 02 May 2006
Posts: 25
|
|
|
|
I executed the same query;
SELECT CURRENT_DATE AS CURRENT_DATE,
SUBSTR('JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',
MONTH(CURRENT_DATE)*3-2,3) AS FORMATTED_DATE2
FROM SYSIBM.SYSDUMMY1
The result was;
CURRENT FORMATTED
DATE DATE2
---------- ---------
2006-08-03 AUG
There is nothing wrong with the query. |
|
| Back to top |
|
|
ranjanp
New User
Joined: 02 May 2006
Posts: 25
|
|
|
|
Also Antonrino,
You cannot get the current date in the format you have given (03.08.2006) value as
CURRENT_DATE ! FORMATTED_DATE2 !
+-----------------------------------------------------+
! 03.08.2006 ! AUG !
executing your query. |
|
| Back to top |
|
|
antonrino.b
New User
Joined: 10 Jan 2006
Posts: 76
Location: Germany
|
|
|
|
Sorry guys,
It was a silly mistake. There should be a space between the comma and the digit 3 in the substr usage.
That was the problem. too silly..
and ranjan,
I get the date in that format only. I think it depends upon the DB2 settings and we can change it.
Thanks,
Antu |
|
| Back to top |
|
|
antonrino.b
New User
Joined: 10 Jan 2006
Posts: 76
Location: Germany
|
|
|
|
Hi ppl,
It's possible to change the date format by changing the territory code.
To change the date format, bind the collection of db2 utility package to use a different date formats.
The supported formats are
DEF - associated with territory code
EUR - European format
ISO - International Standards Organisation
JIS - Japanese Industrial Standard
LOC - local form
USA - IBM standard for US data and time
For more info, click here |
|
| Back to top |
|
|
|
|
