0

I want to select a column which is from datatype "datetime" in Orcale DB in date format.

column
1/31/2006 22:00:00 AM

I tried the following queries and got errors like below

select DATE_FORMAT(column, 'YYYY-MM-DD') as column from table
ORA-00904: "DATE_FORMAT": invalid identifier

select TO_DATE(column, 'YYYY-MM-DD') as column from table
ORA-00918: column ambiguously defined

select TO_DATE(column, 'YYYY-MM-DD') from table
org.apache.avro.SchemaParseException: Illegal character in: to_date(column,'yyyy-mm-dd')

What is the right syntax for this?

Improve this question
5
  • What's the expected result? Commented Aug 26, 2021 at 9:26
  • @jarlh 2006-31-01 Commented Aug 26, 2021 at 9:37
  • Cast(column as DATE) if you want a DATE type for futher date ariphmetic expressions. Commented Aug 26, 2021 at 9:43
  • 3
    Oracle does not have a data type "datetime". It has date and timestamp. My guess is that you mean that the column is of type date. What data type do you want returned? A string "2006-31-01"? A date with the time component set to midnight? Something else? Commented Aug 26, 2021 at 9:56
  • 1
    You've also tagged this mssql-jdbc which is the Microsoft SQL Server JDBC driver. But that driver only works to connect to a SQL Server database. It doesn't make sense in combination with the oracle tag. If you are using the Oracle database, you aren't using the Microsoft SQL Server JDBC driver to connect. And vice versa. If you are connecting to a SQL Server database, there is a datetime data type but the answers will be completely different because SQL Server doesn't have to_char or to_date functions. Commented Aug 26, 2021 at 10:49

3 Answers 3

Reset to default
1

You should be using a to_char to convert a date to a specific string format.

select to_char(dat_column, 'YYYY-MM-DD') as column from table;

If you are expecting a date as return type, then that is more about the client you are using. You could set a session level parameter like this:

alter session set nls_date_format='YYYY-MM-DD';
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Setting the NLS_DATE_FORMAT will not change anything in 3rd-party applications such as C#, Java, PHP, Python, etc. It is used internally within the Oracle database (for implicit casts between strings and dates) and in a few client applications such as SQL/Plus and SQL Developer (which are both Oracle products) as a display format but it does not change that the data type still has year-to-second components.
1

In Oracle, there is no "datetime" data type. There is either DATE or TIMESTAMP and they both have year, month, day, hour, minute and second components (TIMESTAMP also has optional fractional seconds and time zone components). Both of those data types are binary data types and do NOT store a format.

If you want to set the time component of a DATE to midnight then use:

SELECT TRUNC(date_column) AS date_column_at_midnight
FROM   table_name;

If you want to display a DATE column with only year, month and day components then you need to convert to a data type that does support a formatting; i.e. a string.

SELECT TO_CHAR(date_column, 'YYYY-MM-DD') AS formatted_date_string
FROM   table_name;
Improve this answer

Comments

0

To convert a column of datatype TIMESTAMP to datatype DATE use the CAST function (docs) :

select CAST(column AS DATE) as dt_column from table

The error "ORA-00918: column ambiguously defined" indicates that the SQL engine is unable to determine what table the column belongs to. To avoid that make sure to alias all joined tables and use the alias for those columns. Like this:

select CAST(t1.column AS DATE) as dt_column from table t1
Improve this answer

6 Comments

Does it not transforms the column to varchar?
@MirelKhok Yes. However, a DATE always has year-to-second components and is a binary data type that does not store any format so if you want to display it in a different format then you need to convert to a different data type (i.e. a string).
@MireKhok I changed my answer - now I'm showing how to convert timestamp to date. In your question you say "date format" that implies a format but I think you meant "date datatype". Correct me if I'm wrong.
Using CAST will not remove the time component as, in Oracle, a DATE always has year, month, day, hour, minute and second components. The client application may chose not to display them but they are always there. If you want to display the date (or timestamp) without a time component then you need to convert it to a data type that does support formatting such as a string.
I tried "to_char" and I got org.apache.spark.sql.AnalysisException: Undefined function: 'to_char'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.