covert char to date

Mathew_paul · #1 (**permalink**) 10-26-09, 00:25

hi all

i have a char column which is having date in it
while trying to convert to date its not working
any way to covert

for example
select pol_dt from tpol

1
----------
2004/03/15

pol_dt is a char column
when we do
select date(pol_dt) from tpol its giving
1
----------
1

kindly suggest
regds
Paul

wilsonfv · #2 (**permalink**) 10-26-09, 02:50

It's easy.
replace the '/' with '-'

Code:

select date(replace(pol_dt, '/', '-')) from tpol

Mathew_paul · #3 (**permalink**) 10-26-09, 06:03

thax wilson for the reply
but its giving the same o/p
i tried
select date(substr(pol_dt,1,4) || '-' || substr(pol_dt,5,6) || '-' ||
substr(pol_dt,7,8)) from tbname
its not working
regds
Paul

Stealth_DBA · #4 (**permalink**) 10-26-09, 06:22

Paul, Your SUBSTR values are incorrect for the Month and Day.

Code:

1234567890
2004/03/15

The Month starts in position 6 for 2 characters and the Day starts in position 9 for 2 characters.

Code:

DATE( SUBSTR(POL_DT,1,4) || '-' || 
      SUBSTR(POL_DT,6,2) || '-' ||
      SUBSTR(POL_DT,9,2) )

DB2Plus · #5 (**permalink**) 10-26-09, 07:50

Quote:

Originally Posted by Mathew_paul

thax wilson for the reply
but its giving the same o/p
i tried
select date(substr(pol_dt,1,4) || '-' || substr(pol_dt,5,6) || '-' ||
substr(pol_dt,7,8)) from tbname
its not working
regds
Paul

substr(pol_dt,5,6) means substring starting from position 5 with length 6.
Not the characters in position 5 and position 6 how you are thinking.

Kara.

Internet Services

Web Development

Digital Marketing

Consumer Tech