This discussion is archived
7 Replies Latest reply: Feb 24, 2009 10:22 PM by 418213

# How to convert the SYSDATE to number?

Currently Being Moderated
Hi,

How to convert the SYSDATE to number?

For example :

1. It is in number 39867.3671

Now I convert to date

SELECT TO_DATE('01011900','ddmmyyyy')+39867.3671 FROM DUAL

Result : 25/02/2009 08:48:37

How to convert the above date to number ? and SYSDATE to number?

Thanks
Nihar
• ###### 1. Re: How to convert the SYSDATE to number?
Currently Being Moderated
SELECT TO_DATE('01011900','ddmmyyyy')+39867.3671 FROM DUAL
here you have not converted a number to date you have added 39867.3671 days (almost 109 years) to 01/01/1900
• ###### 2. Re: How to convert the SYSDATE to number?
Currently Being Moderated
Nihar,

SOmething like this
`````` SELECT   TO_NUMBER(TO_CHAR (TO_DATE ('01011900', 'ddmmyyyy') + 39867.3671,
'YYYYMMDDHH24MISS'))
my_date_number
FROM   DUAL

SELECT   TO_NUMBER (TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS')) my_sysdate_number
FROM   DUAL;``````
Regards

Edited by: OrionNet on Feb 25, 2009 12:01 AM
• ###### 3. Re: How to convert the SYSDATE to number?
Currently Being Moderated
If you want the number of days from the current day to 01/01/1900 then you can
``````SQL> select sysdate - to_date('01011900','ddmmyyyy') days from dual
2  /

DAYS
----------
39867.4386``````
• ###### 4. Re: How to convert the SYSDATE to number?
Currently Being Moderated
Nihar,

It's confusing what exactly you want number of days or just convert to number, well you got 2 different examples. So if you can elobrate more what exactly are you trying to do that might be more helpful rather us making guesses.

Regards
• ###### 5. Re: How to convert the SYSDATE to number?
Currently Being Moderated
select to_char(sysdate,'j') from dual;
• ###### 6. Re: How to convert the SYSDATE to number?
Currently Being Moderated
Thaks Karthik,

I got what I need form your query. Thanks for your help.
• ###### 7. Re: How to convert the SYSDATE to number?
Currently Being Moderated