Re: datediff

More background on Oracle dates.

Oracle date type fields are a floating point number that represent the date and time. The integer part is the day since some fixed date (sorry, but I don't know/remember that much background) and the stuff to the right of the decimal point is the time in seconds since midnight of the current day.

If you insert a date into a date field using 'to_char' the time part will be equal to midnight. If you use 'sysdate' the time will be the current time of the system. If you do a select on a date field the default is to display just the date portion, which leads to hair pulling when to fields with identical looking values won't compare as equal. To compare just the date part you need to use the 'trunc' function on the date field - this sets the time part to midnight.

And, one last thing that comes to mind, once you subtract one date from another you pretty much are on your own to format, etc, the result. There are no date functions that I know of for working with the resulting number of days between 2 dates.

"Michel Cadot" <micadot_at_netcourrier.com> wrote in message news:9nq00c$v5d$1_at_s1.read.news.oleane.net...
>
> "Jean" <ken_jean_at_hotmail.com> a écrit dans le message news:
9nptaf$t3i$1_at_serv1.iunet.it...
> > Hi to all!!!
> > I'm new in oracle.
> > I need to have a difference between current date and a datetime field
that I
> > have in a table.
> > How I can do this?
> > In SQL Server exist a function called DateDiff that make this.
> > And in Oracle what is solution?
> >
> > Thanks a lot in advance.
> > Bye
> >
>
> With Oracle there is - (minus):
> date2-date1=nb of days between the two dates (float number).
> For example, nb of days since beginning of the year:
>
> v817>select to_date('13/09/2001 11:56:10','DD/MM/YYYY HH24:MI:SS')
> 2 -to_date('01/01/2001 00:00:00','DD/MM/YYYY HH24:MI:SS') diff
> 3 from dual;
>
> DIFF
> ----------
> 255.497338
>
> --
> Have a nice day
> Michel
>
>
>
>
Received on Thu Sep 13 2001 - 08:10:10 CDT