CODEWITHSUNDEEP
oracle-databaseoracle11g
muthukumar
muthukumar

Reputation: 2243

Oracle convert Date to Number

I want to convert date to number in oracle 11g .

The date is stored in (12/03/2013 12:23:00).

I want to convert this to Number(The date corresponding number value). As in java we convert date to long .

I want the same to be done here .

    Calendar c = Calendar.getInstance();
    c.set(2013, 05, 23, 0, 0, 0);

    Date date = c.getTime();
    System.out.println("Date is  " + date);
    long longDate = date.getTime();
    System.out.println("Date as long :" + longDate);
    Date d = new Date(longDate);
    System.out.println("Converted Date :" + d);*

The Output is :

**Date is Sun Jun 23 00:00:00 SGT 2013

Date as long :1371916800981

Converted Date :Sun Jun 23 00:00:00 SGT 2013**

Now I want to store value as 1371916800981

Upvotes: 2

Views: 43497

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I am guessing that the long data type that you want is something like the number of seconds or milliseconds since 1970-01-01.

To get this requires just a bit of arithmetic:

select (to_date(s1, 'MM/DD/YYYY HH24:MI:SS') -
        to_date('1970-01-01', 'YYYY-MM-DD')
       ) *24*60*60
from (select '12/03/2013 12:23:00' as s1 from dual
     ) t

I note that your result is using the current time stamp. This might include milliseconds which this constant date format doesn't include.

Upvotes: 4

Jakub P
Jakub P

Reputation: 542

You can achieve this with a help of RAW data type:

SELECT 
  s1,
  to_number(TO_CHAR(s1,'YYYYMMDDHH24MISS')) as s2,
  utl_raw.cast_to_raw(s1) as d1,
  utl_raw.cast_to_raw(s2) as d2,
  round(utl_raw.cast_to_number(utl_raw.cast_to_raw(s1)),20) as s1,
  round(utl_raw.cast_to_number(utl_raw.cast_to_raw(s2)),20) as s2,
  utl_raw.cast_to_number(utl_raw.cast_to_raw(s1)) - utl_raw.cast_to_number(utl_raw.cast_to_raw(s2)) as s1_s2_diff
FROM 
( select 
    sysdate as s1, 
    sysdate-1/24/60/60 as s2 
  from dual );

Output:

S1                  S2      
------------------- --------------
2014.03.16 11:14:16 20140316111416               

D1                                     D2
-------------------------------------- --------------------------------------
323031342E30332E31362031313A31343A3136 323031342E30332E31362031313A31343A3135

S1_1                                   S2_1    
-------------------------------------- --------------------------------------
-53524955535055524769525243,5249435247 -53524955535055524769525243,5249435248

SDIFF     
------------    
0,0000000001

Upvotes: 0

Related Questions