CODEWITHSUNDEEP
sqloracle-database
Devendra S
Devendra S

Reputation: 11

ORA-01722 INVALID NUMBER in oracle

I am getting invalid number error message while executing the below select statement.Can any one have an idea about the issue..Please let me know.

select TO_DATE(TO_CHAR('2015/01/22 00:00:00','YYYY/MM/DD'),'YYYY/MM/DD')

actually i want oracle standard date format without time stamp for this date '2015/01/22 00:00:00'

Upvotes: 0

Views: 25416

Answers (5)

toha
toha

Reputation: 5510

I suggest you not to give hour-minute-second if you do not want to show the time.

This is my simplest answer :

SELECT TO_DATE('2015/01/22','YYYY/MM/DD') FROM dual

Query Result

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36807

Try using date literals with the standard ISO 8601 format.

date '2015-01-22'

Upvotes: 0

David Faber
David Faber

Reputation: 12485

actually i want oracle standard date format without time stamp for this date '2015/01/22 00:00:00'

I'm not sure what you mean by "Oracle standard date format." The format in which a date would appear would be based on your NLS settings (in particular, NLS_DATE_FORMAT). If you are just trying to format this string representing a date, then you might want something like the following:

SELECT TO_CHAR(TO_DATE('2015/01/22 00:00:00','YYYY/MM/DD HH:MI:SS'), 'YYYY/MM/DD')
  FROM dual;

That is, you have the TO_CHAR() and TO_DATE() functions in the wrong order, and an incomplete date mask for the call to TO_DATE().

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

select to_date('2015/01/22 00:00:00','YYYY/MM/DD HH24:MI:SS') as dt
from dual

Fiddle - http://sqlfiddle.com/#!4/6a3a6/1/0

As an FYI, the Oracle DATE data type does include the time component (just not down to fractional seconds, as is the case with the TIMESTAMP data type).

If you are converting values and want to bring all the time values to zero you can use the trunc function like this (which changes 12:07:00 to 00:00:00):

select trunc(to_date('2015/01/22 12:07:00','YYYY/MM/DD HH24:MI:SS'),'DD') as dt_with_time_zerod
from dual

Fiddle - http://sqlfiddle.com/#!4/6a3a6/2/0

If the source is itself a date and you want to convert the date to a string in the Oracle default date format ('DD-MON-RR') you can achieve that by running:

select to_char(trunc(to_date('2015/01/22 12:07:00','YYYY/MM/DD HH24:MI:SS'),'DD'),'DD-MON-RR') as dt_with_time_zerod
from dual

Fiddle - http://sqlfiddle.com/#!4/6a3a6/3/0

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

If it's a date field, to_char without a mask will give you what you say you want.

Upvotes: 0

Related Questions