Date function in oracle sql

Question

What is the difference between next two query in Oracle DB (11g Enterprise Edition Release 11.2.0.1.0):

SELECT DATE '2018-02-01'  FROM DUAL;

and

SELECT DATE('2018-02-01') FROM DUAL;

Where the first one work fine, but the next one I get next error:

ORA-00936: missing expression

Thanks BR

There is no function date() - you probably want to_date() — user330315
– user330315, Commented Feb 1, 2018 at 8:44
The same difference that between N'foo' and N('foo'). Not everything that has parenthesis is a function call, but all function calls need parenthesis. — Álvaro González
– Álvaro González, Commented Feb 1, 2018 at 8:50

Aleksej · Accepted Answer · 2018-02-01 10:08:05Z

2

From Oracle docs:

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. To specify a DATE value as a literal, you must use the Gregorian calendar. You can specify an ANSI literal, as shown in this example:
DATE '1998-12-25'
The ANSI date literal contains no time portion, and must be specified in the format 'YYYY-MM-DD'. Alternatively you can specify an Oracle date value, as in the following example:
TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')

Please notice that, according to Wernfried Domscheit, using only 2 digits for years is not a great idea. If you want to use a to_date, you would better use a 4-digit format:

TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI')

edited Feb 1, 2018 at 10:08

answered Feb 1, 2018 at 8:46

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

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

2 Comments

Wernfried Domscheit Over a year ago

Using only 2 digits for year is not very smart. Your example is the best proof - did you like to get 2098-12-25 rather than 1998-12-25?

Aleksej Over a year ago

@WernfriedDomscheit: It's from Oracle documentation :). Edited to clarify, thanks

Collectives™ on Stack Overflow

Date function in oracle sql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related