Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SQL query to display 01/29/2006 as JAN 29

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Thu Jul 27, 2006 6:39 pm    Post subject: SQL query to display 01/29/2006 as JAN 29
Reply with quote

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
View user's profile Send private message

Prajesh_v_p

Active User


Joined: 24 May 2006
Posts: 133
Location: India

PostPosted: Fri Jul 28, 2006 3:11 pm    Post subject:
Reply with quote

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
View user's profile Send private message
mahsug

New User


Joined: 01 Jul 2003
Posts: 15
Location: Phoenix , AZ

PostPosted: Fri Jul 28, 2006 5:45 pm    Post subject: Re: SQL query to display 01/29/2006 as JAN 29
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Fri Jul 28, 2006 7:40 pm    Post subject: Re: SQL query to display 01/29/2006 as JAN 29
Reply with quote

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
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Mon Jul 31, 2006 4:47 pm    Post subject: Re: SQL query to display 01/29/2006 as JAN 29
Reply with quote

Thats Cool david.Excelent Logic!!!
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Tue Aug 01, 2006 11:34 am    Post subject: Re: SQL query to display 01/29/2006 as JAN 29
Reply with quote

Yes thats an excellent logic David
Thanks
Back to top
View user's profile Send private message
antonrino.b

New User


Joined: 10 Jan 2006
Posts: 76
Location: Germany

PostPosted: Thu Aug 03, 2006 2:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ranjanp

New User


Joined: 02 May 2006
Posts: 25

PostPosted: Thu Aug 03, 2006 7:32 pm    Post subject: Re: SQL query to display 01/29/2006 as JAN 29
Reply with quote

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
View user's profile Send private message
ranjanp

New User


Joined: 02 May 2006
Posts: 25

PostPosted: Thu Aug 03, 2006 7:36 pm    Post subject: Re: SQL query to display 01/29/2006 as JAN 29
Reply with quote

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
View user's profile Send private message
antonrino.b

New User


Joined: 10 Jan 2006
Posts: 76
Location: Germany

PostPosted: Thu Aug 03, 2006 7:58 pm    Post subject:
Reply with quote

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
View user's profile Send private message
antonrino.b

New User


Joined: 10 Jan 2006
Posts: 76
Location: Germany

PostPosted: Thu Aug 03, 2006 8:03 pm    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts display the sign only if the value is... useit DFSORT/ICETOOL 3 Wed Oct 04, 2017 5:53 pm
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Binary to display characters Roy Ware SYNCSORT 3 Tue Sep 19, 2017 8:46 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us