Char to Date Convertion

Ganesh K Rajan · New User Joined: 13 May 2005 Posts: 7

Hi,
I wanted to convert date declared as char(7)(in CYYMMDD) to date format (YYYYMMDD).
I used the following query :

SELECT DISTINCT POLICY_NO,TRANS_DTE, TO_DATE((SUBSTR(TRANS_DTE,2,6)),''YYYYMMDD'') FROM DB2PADM using TO_DATE function but it gives following error.

SQLCODE = -171,
ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 2 OF TO_DATE IS INVALID
Can anyone suggest any other way.

Rgs,
Ganesh.

prasannanjaneya · New User Joined: 14 Mar 2005 Posts: 10 Location: pune

hi,

i have a doubt. in char(7) c stands for what.

prasannanjaneya · New User Joined: 14 Mar 2005 Posts: 10 Location: pune

HI,

I THINK TO_DATE RETURNS ONLY TIME-STAMP VALUE. U HAD GIVEN A VALUE WHICH IS NOT IN TIMESTAMP FORMAT. PLEASE THINK IN THIS WAY I HOPE U WILL GET SOLUTION FOR UR ERROR.

IF ANY BODY FEEL THIS IS WRONG PLEASE EXPLAIN THE CORRECT ONE TO ME.

THANKS,
PRASANNA.