|
View previous topic :: View next topic
|
| Author |
Message |
Niki
Active User
Joined: 20 Sep 2008
Posts: 106
Location: Bangalore
|
|
|
|
Hi all,
I am having the TS value = 1993-06-07-14.52.43.580583
when i use the fuction DATE(UPDT_TS) the value is coming as 1993-06-07 (YYYY-MM-DD)
Is there any other fuction which can get me the date in MM/DD/YYYY Format. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Take a look at the ISO functions.
Any reason you can not manipulate date in your program. |
|
| Back to top |
|
|
senthilnathanj
New User
Joined: 31 Jul 2007
Posts: 47
Location: chennai
|
|
|
|
hi niki,
u can write the separate function for date conversion.
| Code: |
DATE_TO_DATE10: PROC(WS_DATE) RETURNS(CHAR(10));
DCL WS_DATE CHAR(26);
WS_DATE10 = (10)' ';
IF WS_DATE ¬= '' THEN
DO;
WS_DATE10 = SUBSTR(WS_DATE,5,2) || '/' ||
SUBSTR(WS_DATE,7,2) || '/' ||
SUBSTR(WS_DATE,1,4);
END;
ELSE
WS_DATE10 = WS_DATE;
RETURN(WS_DATE10);
END; |
just call the functiion
DATE_TO_DATE10(xxxx); |
|
| Back to top |
|
|
murugan_mf
Active User
.jpg)
Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India
|
|
|
|
| Code: |
SELECT
SUBSTR(CHAR(DATE(UPDT_TS)),6,2)|| '/' ||
SUBSTR(CHAR(DATE(UPDT_TS)),9,2)|| '/' ||
SUBSTR(CHAR(DATE(UPDT_TS)),1,4) FROM TBL1; |
|
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi Niki
Try this
SELECT CHAR(SUBSTR(UPDT_TS,1,10), USA) FROM TABLE1;
The SUBSTR function is used because the argument is not a pure date datatype. If the argument is date datatype then you can avoid the use of the function SUBSTR.
DB2 is providing a number of functions to satisfy this type of requirements. Try to refer the DB2 manuals.
Please post the replay if you are able to get the desired result.
Regards
Raghu |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi Nikki
There is a correction in my previous post.
The correct statement is SELECT SUBSTR(CHAR(UPDT_TS,USA),1,10) FROM TABLE1
Regards
Raghu |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
Why not just
| Code: |
| SELECT CHAR(DATE(UPDT_TS),USA) FROM TABLE1 |
|
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi Craq
That is absolutely the shortest sql fro the requirement.
Thanks
Raghu |
|
| Back to top |
|
|
amitgoel29
New User
Joined: 05 Nov 2008
Posts: 3
Location: india
|
|
| Back to top |
|
|
|
|
