CODEWITHSUNDEEP
oracle-database
netraider
netraider

Reputation: 257

Oracle - Date Format

I need your assistance with converting Oracle dates.

I have a column that stores dates like this 20150731 00:00:34.220. However, I would like to show the column like this 20150731 but when I run a simple select statement to test output I get the following error. 

select TO_DATE('20150731 00:00:34.550','YYYYMMDD')
from dual

Error

ORA-01830: date format picture ends before converting entire input string

Upvotes: 0

Views: 332

Answers (5)

Dmitriy
Dmitriy

Reputation: 5565

This query 

select TO_DATE('20150731 00:00:34.550','YYYYMMDD')
from dual

leads to error

ORA-01830: date format picture ends before converting entire input string

because you pass string with length 22 characters, but at the same time you pass date format with 8 characters, which obviously doesn't correspond to string. You should write the query as

select to_timestamp('20150731 00:00:34.550','yyyymmdd hh24:mi:ss.ff3')
  from dual

As for your table, since you have varchar2 column with dates, you have to take care about table content. Query requires exect matching of the source string and date format.
If you want to show only date without time and you don't need to process this string as date, you can make just

select substr('20150731 00:00:34.550', 1, 8)
  from dual

Upvotes: 1

EdStevens
EdStevens

Reputation: 3872

What is the data type of the column? If it is DATE (as it should be) then not it is not stored in the format you say. It is stored in an internal binary format. You would/should use the to_char function to DISPLAY it in whatever format you choose. If you do not use the to_char function, it will be displayed in the format specified by NLS_DATE_FORMAT, which can be specified at several locations.

As for your example, you passed a string format of yyyymmd hh:mi:ss.fff', but you provided a description mask of only YYYYMMDD. It doesn't know what to do with time component. In addition to that when you SELECT TO_DATE, oracle also has to do an implied TO_CHAR to convert it back to a string for display purposes.

In addition, you provided your to_date with a character string that included fractions of seconds. A DATE data type only resolves to seconds. If you need fractional seconds, you need to use TIMESTAMP, not DATE.

Upvotes: 1

The Crimson Wasp
The Crimson Wasp

Reputation: 1

Working on the assumption that you're not trying to change the value in the column, and are just trying to show it in the YYYYMMDD format -

As mentioned by a_horse_with_no_name, you'll just need to convert it to a character string. In this example I used systimestamp as my date:

SELECT TO_CHAR(systimestamp,'YYYYMMDD') FROM DUAL

Result:

20160121

That should give you the YYYYMMDD format you want to display.

Upvotes: 0

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

Is that being stored in an Oracle datetime column? If not, you may have to do some manipulation to get it into a DD-MON-YYYY format. If it is being stored as a text string you could use SUBSTR( Date_field, Start_Position, Length) to get the first 8 characters. check out this link SUBSTR

Upvotes: 0

Aleksej
Aleksej

Reputation: 22969

If your column is a varchar and you need a date output:

select TO_DATE(substr('20150731 00:00:34.550', 1, 8),'YYYYMMDD') from dual

If it's in a date format and you need a string output:

select to_char(your_column, 'YYYYMMDD') from your_table

Upvotes: 0

Related Questions