CODEWITHSUNDEEP
sqloracle11g
Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

Date value returns random integer - oracle

select 31/10/2016 as date from dual;

Retuns me a long integer value.

Whereas,

select '31/10/2016' as date from dual;

Gives me the required result.

What is the transformation happening when we give the date value without quotes?

Upvotes: 2

Views: 83

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44961

Non of them are right.

The 1st is an arithmetic computation.

31/10/2016 = ((31 / 10) / 2016) = 3.1 / 2016 = 0.00153...

The 2nd is a string

select '31/10/2016' + 1 from dual;

ORA-01722: invalid number

ANSI/ISO Dates literals are written as 

DATE 'YYYY-MM-DD'

Datetime Literals

In your case

DATE '2016-10-31'

select DATE '2016-10-31' + 1 from dual;

2016-11-01 00:00:00

P.s.
date is a reserved word.
You couldn't have used it as an alias for your columns without getting a parsing error.

B Oracle Reserved Words, Keywords, and Namespaces

Upvotes: 3

XING
XING

Reputation: 9886

When you execute

select 31/10/2016 as date from dual;

Oracle interprets it as a airthmatic division operation and divides 31 by 10 and result by again 2016 .

When you execute

select '31/10/2016' as date from dual;

Oracle interprets as it as a string and it will throw error since Date is a reserved keyword and you cannot use reserve keywords either as column names or as alias.

The right way to do it as below:

select to_date('31/10/2016','dd/mm/yyyy') as dte from dual;

Upvotes: 2

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

select 31/10/2016 : Its arithmetic operations ( dividation )

select '31/10/2016' : Using quotes its become string. Single quotes delimit a string constant or a date/time constant.

Upvotes: 2

Related Questions