14 Replies Latest reply on Feb 4, 2010 2:44 PM by djboracle

    Convert number to date

    75825
      Hi;

      I have a problem. I want to convert from number to date. How can I convert. If you know, help me please..

      Thanks
        • 1. Re: Convert number to date
          InoL
          select to_date('20030930','yyyymmdd')
          from .....
          • 2. Re: Convert number to date
            75825
            Thank you for answer but your answer not is true. My problem :
            37894(data)=30/09/2003 (you can try to convert excel)

            what can I do it seems date format

            Thanks..
            • 3. Re: Convert number to date
              InoL
              37894 is some internal representation of Excel.
              What do you want to do? Do you want to load an Excel sheet in the database?
              Format the column first in a normal date format and save the Excel sheet as a csv file.

              • 4. Re: Convert number to date
                75825
                Hi,
                I want to do that, I have a table and date cloumn. But date column type number. The cloumn data like before mail. I want to convert number to date format type. How can I do..
                • 5. Re: Convert number to date
                  75825
                  Hi,
                  I want to do that, I have a table and date cloumn. But date column type number. The cloumns data like before mail. I want to convert number to date format type. How can I do.. I don't want to load excel.

                  Thanks.

                  e-mail : hakan.ozdemir@ttnet.net.tr
                  • 6. Re: Convert number to date
                    393139
                    Hi Hakan,

                    In Miscrosoft Excel, by default, numbers are used to represent dates. Excel uses the following format to store numbers:

                    1st Jan 1900 is stored as 1
                    2nd Jan 1900 is stored as 2
                    and so on.... Every subsequent number represents the next date.

                    If you extend this, you will see that 37894 represents 30 Sep 2003.

                    So as Ino said, it is an internal Excel representation. Other software like Oracle Database do not accept this particular format, but will have their own representation. So there is no direct way to convert numbers to dates inside non-Excel software. You can convert Excel to convert all these dates and store them back in the database as dates.

                    Navneet.
                    • 7. Re: Convert number to date
                      204910
                      1* select (to_date('01011900','DDMMYYYY')-1)+37894 from dual
                      SQL> /

                      TO_DATE('3
                      ----------
                      01.10.2003

                      Maybe it should return 30.9.2003 but Oracle8i 8.1.7.4.0 doesn't know date 29.2.1900 (Excel does). Was the year 1900 a leap year?

                      Rado.
                      • 8. Re: Convert number to date
                        204910
                        1* select (to_date('01011900','DDMMYYYY')-1)+37894 from dual
                        SQL> /

                        TO_DATE('3
                        ----------
                        01.10.2003

                        Maybe it should return 30.9.2003 but Oracle8i 8.1.7.4.0 doesn't know date 29.2.1900 (Excel does). Was the year 1900 a leap year?

                        Rado.
                        • 9. Re: Convert number to date
                          393139
                          Hi Rado,

                          Thanks for the formula. 1900 was not a Leap Year:
                          http://www.uic.edu/depts/accc/software/isodates/leapyear.html

                          Navneet.
                          • 10. Re: Convert number to date
                            462110
                            you can create a oracle function for this.

                            CREATE OR REPLACE FUNCTION GETDATENUMBER(TMPVAR DATE) RETURN NUMBER IS
                            VAL NUMBER;
                            BEGIN
                            SELECT TRUNC(TMPVAR - TO_DATE('01011900')+2) INTO VAL FROM DUAL;
                            RETURN VAL;
                            END;
                            /

                            if you want convert number to date you can this

                            CREATE OR REPLACE FUNCTION GETNUMBERDATE(TMPVAR NUMBER) RETURN DATE IS
                            VAL DATE;
                            BEGIN
                            SELECT TO_CHAR(39014 + TO_DATE('01011900')- 2,'dd.mm.yyyy') INTO VAL FROM DUAL;
                            RETURN VAL;
                            END;
                            /


                            bye
                            • 11. Re: Convert number to date
                              615237
                              hi,

                              i have a number like this 1198846200000(note this is Number datatype, not timestamp) how to convert it to date?

                              plz help me..

                              Thanks
                              • 12. Re: Convert number to date
                                450441
                                do you know what date it's supposed to be? I can't see any obvious clues.
                                • 13. Re: Convert number to date
                                  678690
                                  excel = excel's number version of the date

                                  formatdatetime(dateadd("d",cint(excel)-2,"1/1/1900"),1)

                                  I had to subtract 2 as the date was off by 2 days. Not sure why, but it works.

                                  Format: Wednesday, January 7, 2009.
                                  • 14. Re: Convert number to date
                                    djboracle
                                    These functions will convert and Oracle date to a Serial (Numeric) date, and vice versa. Excel uses Serial Date format:

                                    http://www.jasonsdevelopercorner.com/?p=209

                                    CREATE OR REPLACE FUNCTION convertOracleToSerialDate(p_date DATE) RETURN NUMBER
                                    IS
                                    BEGIN

                                    RETURN (p_date – TO_DATE(‘01-JAN-1900′))+2;

                                    END;
                                    /

                                    CREATE OR REPLACE FUNCTION convertSerialToOracleDate(p_serial_date NUMBER) RETURN DATE
                                    IS
                                    BEGIN

                                    RETURN (TO_DATE(‘01-JAN-1900′) + p_serial_date)-2;

                                    END;
                                    /