Sql Trunc Math issue

Question

When I using

select trunc(sysdate) from dual

I got the correct date which is today in a good format. like "6/24/2013"

And if I do

select trunc(sysdate)-1 from dual

I got yesterday date with a good format.

However, if i do:

select trunc(sysdate)- trunc(sysdate-1) from dual

I suppose to get 1, but what i got is /d/yyyy

All I want to do is calculate how many days between two dates. By using Trunc(date) - Trunc(another date), I always got /d/yyyy rather than the days between this two dates.

How can do that?

Thanks in advance.

Is this a duplicate of http://stackoverflow.com/questions/1646001/how-can-i-get-the-number-of-days-between-2-dates-in-oracle-11g — Rob Kielty, Jun 24 '13 at 20:22
If I run the SQL statement you indicate you're running in SQL*Plus, I get a result of `1`. Can you edit your post to include a cut and paste from a SQL*Plus session that shows exactly what you are running and exactly what result you get. — Justin Cave, Jun 24 '13 at 20:23
Your query should work and return 1, can you post actual date values you are using. [Working demo here](http://sqlfiddle.com/#!4/d41d8/12699) — rs., Jun 24 '13 at 20:24

score 1 · Answer 1 · answered Jun 24 '13 at 20:36

1

Oracle has a days_between function: DAYS_BETWEEN(date1, date2), so your query could be expressed as:

select abs(days_between(trunc(sysdate),trunc(sysdate-1))) from dual

I have included the abs() function as I can't remember off hand whether the most recent date should go first or second to get a positive result and don't have access to check right now.

answered Jun 24 '13 at 20:36

ChrisProsser

12,598
6
35
44

I got unknow function dats_between – Jay Zhang Jun 24 '13 at 20:49
No it doesn't... except [this](http://docs.oracle.com/cd/A60725_05/html/comnls/us/per/ffugax15.htm). The RDBMS doesn't have, or need, that function. – Alex Poole Jun 24 '13 at 20:49
I have used this and Months_Between in Oracle before. Here is a link with further details: http://docs.oracle.com/cd/A60725_05/html/comnls/us/per/ffugax15.htm – ChrisProsser Jun 24 '13 at 20:51
That's what I linked to, it isn't core SQL. [`months_between` is though](http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions102.htm). – Alex Poole Jun 24 '13 at 20:54

Jon Spokes · Answer 2 · 2013-06-24T20:28:04.580

0

Just use minus (-) Oracle stores dates as numbers and 1 = 1day

so sysdate + 1 is tomorrow, sysdate - 1 is yesterday

From SQL fiddle TRUNC(SYSDATE)-TRUNC(SYSDATE-1) 1

edited Jun 24 '13 at 20:28

answered Jun 24 '13 at 20:23

Jon Spokes

2,599
2
18
21

That's what the OP says he's doing? – Alex Poole Jun 24 '13 at 20:31
And if you try it it works ok?. Is there any chance of getting a failing example on SQL Fiddle? – Jon Spokes Jun 24 '13 at 20:44

Sql Trunc Math issue

2 Answers2