I have a string '20101023101500' representing the date and time in a CSV file. The table I'm loading this into has a column of 'date' data type so I'm using the following to convert the string into date data type:
What I want to do is, somehow omit the 'seconds' '00' i.e., not load the seconds.
I tried using to_char but then the date column will not accept the string.
I can't (don't want to) manipulate the string in the CSV file either.
The nls_date_format is DD/MM/RR.
Hope I've given the whole picture. Could someone please help?
I take it changing the data type of the DATE column to VARCHAR(2) when it already contains date entries will not help either?
Help in what?
Help in allowing storing "dates" not existing in reality like '20100431183000' (April, 31st)? Yes.
Help in increasing space for storing it? Yes, as storing this string (without seconds) will need at least 12 characters (so VARCHAR2(2) will not be able to keep it), while DATE data type is stored in 7 bytes.
You were talking about CSV file, but without describing the way how it is accessed in Oracle (external table?), it is really hard to propose anything. By the way, in the example in your first post, 20101023101500 is a numeric literal, not a string string one; but TO_DATE is probably (another assumptions) not used on literals, so it is confusing. Why do you not post verbatim (with eliminating some parts that are not important in your case) what you have? Or just create one table with DATE column only, CSV file containing only this date and construct a simple test case fully covering your issue?
changing the data type of the DATE column to VARCHAR(2)
Don't be silly! Never store date values into columns whose datatype is different from DATE. Storing dates into CHAR columns is simply stupid, and all you can expect from doing that is bunch of problems.
select to_date(substr('20101023101500',1,12),'YYYYMMDDHH2 4MI') from dual
select trunc(to_date(20101023101500,'YYYYMMDDHH24MISS'),' mi') from dual
10/23/2010 10:15:00 AM
Where? What is the tool you are using (sqlplus, SQL Developer, Forms, JDBC, ...)?
Please realize, that DATE data type has no format and contains date/time components with second granularity.
Repeat this sentence until you accept it.
If you want to display the column with DATE data type in specific format, use proper setting. E.g. NLS_DATE_FORMAT in sqlplus, the same (editable in Preferences window) in SQL Developer, cell property in Forms etc.
Alternatively, you may use TO_CHAR to convert it explicitly to string, but then you may have problem if you will want to treat it further, as the result will have VARCHAR2 data type. Still curious how it is related to that CSV file remark in your first post.