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
– Gordon Linoff, Commented Oct 3, 2017 at 16:24

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 3, 2017 at 16:26

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sagi Over a year ago

If he’d use a subquery or parenthesis won’t it work though ?

sagi Over a year ago

Rextester says no haha.

fleetmack Over a year ago

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.

Lukasz Szozda Over a year ago

@fleetmack The key point is that literal is for fixed data value. I will prepare dynamic demo

Collectives™ on Stack Overflow

ansi SQL date function in Oracle

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related