CODEWITHSUNDEEP
sqloracle-databaseto-date
fleetmack
fleetmack

Reputation: 378

ansi SQL date function in Oracle

I realize I could use a combination of to_char and to_date and what not as a work-around, but I'm trying to figure out why this doesn't work. I am using Oracle 12.1

 select '2016-10-01'
 from dual

union all

select to_char(2016)||'-10-01'
 from dual;

Each side of the union produces identical output: 2016-10-01. If I then try to use the ANSI date syntax (as described here: http://blog.tanelpoder.com/2012/12/29/a-tip-for-lazy-oracle-users-type-less-with-ansi-date-and-timestamp-sql-syntax/ ), it only works on the first one, not the second one:

 select date '2016-10-01'
 from dual

This returns: 10/1/2016

But if I try this:

 select date to_char(2016)||'-10-01'
   from dual;

I get on:

ORA_00936 missing expression error.

I can code a work-around, but I'm stumped as to why one works and the other does not.

Upvotes: 3

Views: 1967

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175786

It won't work that way because DATE is not a function but a literal.

The terms literal and constant value are synonymous and refer to a fixed data value.

Date Literals

You can specify a DATE value as a string literal, or you can convert a character or numeric value to a date value with the TO_DATE function. DATE literals are the only case in which Oracle Database accepts a TO_DATE expression in place of a string literal.

You could use TO_DATE function.

select TO_DATE(to_char(2016)||'-10-01', 'YYYY-MM-DD')
from dual;

Rextester Demo

EDIT:

Using dynamic-SQL:

DECLARE
  i DATE;
  stmt VARCHAR2(100);
BEGIN
  stmt := q'{SELECT  DATE '}' || TO_CHAR(2016) || '-01-01' ||  q'{' FROM dual}';

  EXECUTE IMMEDIATE stmt INTO i;
  DBMS_OUTPUT.PUT_LINE('i =>' || i);
END;

Upvotes: 4

Related Questions