Special/French Characters Conversion

Sometimes you may get special/french characters in your data which can cause
'ORA-06502: PL/SQL: numeric or value error: character string buffer too small' error.

Example : ÖÄ é è
Reason   : These single characters taking two byte length.

To overcome this issue, either increase the length of your column(sometimes it is not advisable due to your business need/design) or use convert function to convert these characters.

--> Get the NLS Character set defined
select value
  from nls_database_parameters
where parameter='NLS_CHARACTERSET';

--> Use Convert function
CONVERT(<char>, <destination_char_set>, <source_char_set>)

> Use destination Character set as 'US7ASCII' -> US 7-bit ASCII character set
> Use Source Character set as Character set returned above.

Example : select convert('Repport ÖÄ é è  de öäå','US7ASCII','<char set returned above>')
                  from dual;

>> If it will not find any valid conversion for any character it will put '?' for that character

Dates in Hours/Minutes/Secs

--> Extract date/hours/minutes/seconds/mseconds from a date
--------------------------------------------------------------

select to_char(day, 'dd-mon-yyyy hh24:mi:ss' )                 Input_Date,
         trunc(day)                                                             Day,
         trunc(time*24)                                                        Hours,
         trunc(time*24*60 - 60*trunc(time*24))                       Minutes,
         trunc(time*24*60*60 - 60*trunc(time*24*60))              Seconds,
         trunc(time*24*60*60*100 - 100*trunc(time*24*60*60)) mseconds
  from (select &day                      day,
                    &day - trunc(&day)  time
             from dual
          ) 

--> Get difference of two dates in days/hours/minutes/seconds/mseconds
-------------------------------------------------------------------------

select to_char( date1, 'dd-mon-yyyy hh24:mi:ss' )     Date1,
          to_char( date2, 'dd-mon-yyyy hh24:mi:ss' )     Date2,
          trunc( date2-date1 )                                      Days,
          trunc( mod( (date2-date1)*24, 24 ) )                Hours,
          trunc( mod( (date2-date1)*24*60, 60 ) )           Minutes,
          trunc( mod( (date2-date1)*24*60*60, 60 ) )      Seconds,
          trunc( mod( (date2-date1)*24*60*60*60, 60 ) ) mSeconds
  from (select date_column1 date1
                    ,date_column2 date2
            from &table_name
          )

--> Adding specific number of Days/Hours/Minutes/Secs to a Date
--------------------------------------------------------------------
 select sysdate inputdate
         , sysdate + (:day) + (:Hours/24) + (:Minutes/(24*60)) + (:Secs/(24*60*60)) newdate
   from dual;