# Database Discussions > Oracle >  Converting date to float

## tyaramis

Hi all

How can I convert a datetime field into Float type in oracle? 

START_DATE : datetime field

With sql server its like;

Select CONVERT(FLOAT, START_DATE)

How can I write same statament for oracle?

Thanks in advance...

Tolga

----------


## oraclable

a simple way to do it is:
TO_NUMBER(TO_CHAR(start_date,'yyymmdd')) 


or you can store your own function in the db:
CREATE OR REPLACE FUNCTION dateToFloat(p_date IN DATE, p_format IN VARCHAR) RETURN NUMBER IS
	v_float number;
	BEGIN
		select TO_NUMBER(TO_CHAR(p_date,p_format)) into v_float from dual;
		return v_float;
	END;
/

so, you can call that function in your query like:
select dateToFloat(start_date,'mmddyyyy') from your_table;

----------


## tyaramis

First of all thank u for ur answer...

I tried it, it works but not properly..Actually it converts date to a number but not into a float, or double..

its like this;

With sql server, If I make convertion like this;

select CONVERT(FLOAT, convert(datetime,'2003-07-14 00:00:00.000')) AS STARTDATE

I am getting 37814.0. the number value of this date.

if I write this for oracle;
select TO_NUMBER(TO_CHAR('2003-07-14 00:00:00.000','yyyymmdd')) AS STARTDATE

I am getting the value: 20031024


As I see, it formats the date but not convert to float. Of course 20031024 is also a number, but not like sql server. The real number value of this date is 37814.0.

What can be problem?

Thanks again...

----------


## Belayer

What you are looking for is the DayNumber of a given date; that is the number of elapsed days form a base date.  Oracle uses the Julian data format specification for this value with a base date of Jan 1, 4712 BC. Thus 

CREATE OR REPLACE FUNCTION dateToDayNumber(p_date_in IN DATE)
RETURN PLS_Integer IS
      DayNumber  PLS_Integer ;
BEGIN
      DayNumber := TO_NUMBER(TO_CHAR(p_data_in,J));  
      return DayNumber;
END;

Microsoft user Jan 1, 1900 for the base date, thus 

CREATE OR REPLACE FUNCTION dateToMSDayNumber(p_date_in IN DATE)
RETURN PLS_Integer IS
 DayNumber     PLS_Integer ;
 MSDay0Number  PLS_Integer
 MSDay0        Date   := TO_DATE( 19000101, yyyymmdd) ; 
BEGIN
 MSDay0Number :=TO_NUMBER(TO_CHAR(MSDay0,J));  
 DayNumber          := TO_NUMBER(TO_CHAR(p_data_in,J));  
  return ( DayNumber  MSDay0Number) ; 
END;


 That specification gives the number

----------

