Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

how can I format DATE field use SQL

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

New User


Joined: 27 May 2010
Posts: 43
Location: da lian.china

PostPosted: Thu Jan 07, 2016 2:20 pm    Post subject: how can I format DATE field use SQL
Reply with quote

I want to format DATE field use sql

data in table:

Object_date
2015/01/01
2015/01/02
2015/01/03

Result I want by SQL SELECT:

Result_date
2015-01-01
2015-01-02
2015-01-03

Both the attribute is CHAR(10). Is there any way without use complicate function like YEAR(Object_date) ||'-'||Month(Object_date)||'-'||Day(Object_date)?
Back to top
View user's profile Send private message

Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1228
Location: Israel

PostPosted: Thu Jan 07, 2016 7:57 pm    Post subject:
Reply with quote

I have already recommended the use of the VARCHAR_FORMAT function,
however I read somewhere that in some versions of SQL it works only with TIMESTAMP, not DATE.
You'll have to try and tell us.
Back to top
View user's profile Send private message
liying

New User


Joined: 27 May 2010
Posts: 43
Location: da lian.china

PostPosted: Fri Jan 08, 2016 6:48 am    Post subject:
Reply with quote

Marso wrote:
I have already recommended the use of the VARCHAR_FORMAT function,
however I read somewhere that in some versions of SQL it works only with TIMESTAMP, not DATE.
You'll have to try and tell us.


Thank you for your reply.

VARCHAR_FORMAT do not work with DATE type filed format. I have find the way like this .

SELECT REPLACE(START_DATE,'/','-') FROM tabl1
FETCH FIRST 100 ROWS ONLY

And my problem have been solved. Thank again for your attention.
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1679
Location: NY,USA

PostPosted: Sun Jan 10, 2016 12:38 am    Post subject:
Reply with quote

solution to this have been already discussed multiple times on this forum before even you post such questions should have checked to reduce the duplicates.
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 Alter &DATENS field in HEADER1 Angad DFSORT/ICETOOL 4 Mon Apr 24, 2017 11:49 am
No new posts LISTIDR compiled date/time jerryte IBM Tools 3 Thu Apr 20, 2017 7:37 pm
No new posts SUBSTR for Date functions Shaheen Shaik DB2 4 Thu Apr 06, 2017 2:14 pm
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts How to pick only YYMMDD from DATE1P (... atulbaviskar SYNCSORT 7 Wed Mar 22, 2017 11:39 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us