ansi SQL date function in Oracle

Question

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.

`date` is a keyword for defining date constants. You don't have a constant following it. — Gordon Linoff, Oct 03 '17 at 16:24

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

4

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;

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 03 '17 at 16:26

Lukasz Szozda

162,964
23
234
275

If he’d use a subquery or parenthesis won’t it work though ? – sagi Oct 03 '17 at 16:31
1

Rextester says no haha. – sagi Oct 03 '17 at 16:40
Good point, I am cleaning up my question, it is definitely not a function. I did try parenthses around the whole thing (ex: date (to_char(2016)||'-10-01') ) but that didn't work either, I thought it would. I appreciate the to_date and your offering a solution, but I'm more asking out of curiosity; it seems both sides of my union would work. I'll code it differently for now. – fleetmack Oct 03 '17 at 16:42
@fleetmack The key point is that literal is for `fixed data value`. I will prepare dynamic demo – Lukasz Szozda Oct 03 '17 at 16:45

ansi SQL date function in Oracle

1 Answers1