sql - Select sysdate-to_date('08-aug-2015','dd-mm-yy' ) from dual; -

in oracle if run select sysdate-to_date('08-aug-2015','dd-mm-yy' ) dual; command on 08-08-2015 @ 5 pm?what output , why?

my output is

          -30.29566 

i want know why there leading space, why negative, , value represent.

you can substitute fixed value sysdate test this:

select to_date('2015-08-08 17:00:00', 'yyyy-mm-dd hh24:mi:ss')   - to_date('08-aug-2015','dd-mm-yy' ) dual;  to_date('2015-08-0817:00:00','yyyy-mm-d ---------------------------------------                              .708333333 

which 17/24; difference between 17:00 , 00:00 on same date.

date arithmetic explained in documentation, , shows result of subtracting 1 date number:

each date value contains time component, , result of many date operations include fraction. fraction means portion of 1 day. example, 1.5 days 36 hours. these fractions returned oracle built-in functions common operations on date data.

if run query today value see depend on server's local time; see (with current date/time added clarity):

select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') now,   sysdate-to_date('08-aug-2015','dd-mm-yy' ) dual;                  sysdate-to_date('08-aug-2015','dd-mm-yy ------------------- --------------------------------------- 2015-07-08 15:14:54                             -30.3646528 

which means august 8th 30 , bit days in future. 30 days in future 2015-08-07 15:14:54. point midnight on 2015-08-08 8 hours, 45 minutes , 6 seconds; (8 * 60 * 60) + (45 * 60) + 6 seconds, or 31506 seconds in total; , there 86400 seconds in day, fraction 31506/86400 = 0.3646528.

so midnight on august 8th 30 + 0.3646528 days when ran query. based on value show, -30.29566, 16:54:15 local time on server when generated value.

it's negative because it's in future - you're subtracting later ('higher') date earlier ('lower') one. it's number there aren't leading spaces; clients left-pad numeric columns, width of default number format mask or length of column header.