CODEWITHSUNDEEP
sqloracle-database
WonderWoman
WonderWoman

Reputation: 33

date FORMAT AND EXTRACTING DATE VALUES

I have tried to convert date formate so, I can extract values for years I want. please help

I have tried this. the date formate was time stamp. code is successful now the date format is dd-mon-rr but, I am not able to use EXTRACT function to get values for years I want. 

MM.NEEDDATE

TO_CHAR((SELECT MM.NEEDDATE FROM DUAL ), 'DD-MON-RR') AS MATERIALNEEDDATE

I tried this to get value for year > 2018 

(EXTRACT(YEAR FROM TO_DATE(MM.NEEDDATE, 'DD-MON-RR'))> 2018)

but I am getting an error like

ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string" *Cause:
*Action:

successful to convert date formate Actual formate is timestamp

TO_CHAR((SELECT MM.NEEDDATE FROM DUAL ), 'DD-MON-RR') AS MATERIALNEEDDATE

unsuccessful to extract date for year >2018 tried -

(EXTRACT(YEAR FROM TO_DATE(MM.NEEDDATE, 'DD-MON-RR'))> 2018)

ERROR ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string" *Cause:
*Action:

The need date column has date values for many years I want the value for year > 2018 only.

Can I do it without formatting date and how ??

Upvotes: 0

Views: 501

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I don't think you need the to_date(), because you are using TO_CHAR() on the column (converting from a string to a string is generally not how the function is used).

Have you tried this?

EXTRACT(YEAR FROM MM.NEEDDATE) > 2018

Or more simply:

MM.NEEDDATE >= DATE '2019-01-01'

Upvotes: 1

Related Questions