CODEWITHSUNDEEP
oracle-databaseoracle11g
ronan
ronan

Reputation: 4672

varchar2 to timestamp

I have the following data 

SELECT T.FEED_TIME from GLRS.FEED_TYPE T 

05:00:00 AM
06:00:00 AM

WHERE FEED_TIME is VARCHAR2(20)

Now I have to add 1 day to calculate next run time and convert it to a timestamp value 

SELECT to_timestamp(T.FEED_TIME,'dd-Mon-yyyy HH24:MI')+1 FROM FEED_TYPE T WHERE T.FEED_CODE = 'CREDIT';

but above query return not a valid month Please suggest

Upvotes: 1

Views: 370

Answers (1)

Erkan Haspulat
Erkan Haspulat

Reputation: 12562

Your varchar2 data and your date format string do not match.

05:00:00 AM <> dd-Mon-yyyy HH24:MI

Looks like your data only contains time, but since you mentioned 'adding a day', I'll take a wild guess and assume the first two characters are your day, and the rest is time. Then you need something like below:

SELECT to_char(to_date(T.FEED_TIME,'dd:HH:MI AM')+1, 'dd:HH:MI AM')
FROM FEED_TYPE T WHERE T.FEED_CODE = 'CREDIT';

Upvotes: 2

Related Questions