Wednesday, January 23, 2013

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;

No comments:

Post a Comment