3 Replies Latest reply: Aug 26, 2014 2:14 PM by Frank Kulash RSS

    Convert Date to Excel DateValue

    SeshuGiri

      I am using this to convert date to excel datevalue, is there a simple way to do this?

       

      select to_date('31/12/2013', 'dd/mm/yyyy') normal_date,
          (trunc(to_date('31/12/2013', 'dd/mm/yyyy')) + CASE
        WHEN trunc(sysdate) >= to_date('01/01/1900', 'dd/mm/yyyy') then
         1
        else
         0
          end) - (to_date('01-01-1900', 'DD-MM-YYYY') - 1) excel_date
         from dual;
      

       

      Source: https://community.oracle.com/thread/1127089?tstart=0

        • 1. Re: Convert Date to Excel DateValue
          Frank Kulash

          Hi,

           

          SeshuGiri wrote:

           

          I am using this to convert date to excel datevalue, is there a simple way to do this?

           

          1. select to_date('31/12/2013', 'dd/mm/yyyy') normal_date, 
          2.     (trunc(to_date('31/12/2013', 'dd/mm/yyyy')) + CASE 
          3.   WHEN trunc(sysdate) >= to_date('01/01/1900', 'dd/mm/yyyy') then 
          4. ...

          Are you really concerned that SYSDATE might be earlier than 1900?

          Do you need to consider SYSDATE at all?  I would expect December 31, 2013 to map to the same value, regardless of what SYSDATE is.

          I think all you need is

           

          dt - DATE '1899-12-30'

           

          where dt is an Oracle DATE on or after March 1, 1900.

          • 2. Re: Re: Convert Date to Excel DateValue
            SeshuGiri

            sorry this is the correct query,

             

            select to_date('31/12/2013', 'dd/mm/yyyy') normal_date,
            'A'|| '|' ||
                  (trunc(to_date('31/12/2013', 'dd/mm/yyyy')) + CASE
                    WHEN trunc(to_date('31/12/2013', 'dd/mm/yyyy')) >= to_date('01/01/1900', 'dd/mm/yyyy') then
                      1
                    else
                      0
                  end) - (to_date('01-01-1900', 'DD-MM-YYYY') - 1) excel_date
              from dual;
            

             

            But with this I am getting datatype conversion error.  because of 'A'|| '|' ||

            • 3. Re: Convert Date to Excel DateValue
              Frank Kulash

              Hi,

              SeshuGiri wrote:

               

              ... But with this I am getting datatype conversion error.  because of 'A'|| '|' ||

              Basically; it's because you're mixing up || and - operations.

               

              Once again, if dt is an Oracle DATE, no earlier than March 1, 1900, then

               

              dt - DATE '1899-12-30'

               

              is the number that Excel will use for the same date.

              If you want to concatenate 'A|' to this, you can do something like this:

               

              SELECT  'A|' || TO_CHAR ( TO_DATE ('31/12/2013', 'DD/MM/YYYY')

                                      - TO_DATE ('30/12/1899', 'DD/MM/YYYY')

                                      )   AS excel_dt

              FROM    dual;