熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

從Unix到Oracle的日期轉換

2013-11-13 15:34:05  來源: Oracle 

  CREATE OR REPLACE FUNCTION Unix_To_Oracle_Date
  (
    p_unix_date IN NUMBER  Range of >=
    p_num_hr_gmt_diff IN PLS_INTEGER   Range of to +
  
  ) RETURN DATE AS
  
  /*
    Function Name:  Unix_To_Oracle_Date
  
    Purpose:     To calculate an Oracle date based on a Unix GMT date
             in seconds since the epoch of Unix (January st )
  
             The date returned will be based on the number of hours
             the oracle database system clock is either ahead or behind
             the Greenwich Mean Time line
  
             If the parameters do not fall within the specified ranges
             then the returned date will be NULL
  
    Developer Name:  Giovanni Jaramillo
    Developer Email: G
    Creation Date:  Copyright ? December
  
    Parameters:    p_unix_date of NUMBER type (Range is > only)
             p_num_hr_gmt_diff of PLS_INTEGER type (Range to +)
  
    Input:      NONE
    Output:      NONE
    Returned value:  DATE := Unix_To_Oracle_Date(p_Unix_Date);
  
  */
  
    CONSTANTS
  
    These CONSTANTS below are of POSITIVE type to indicate that they
    CANNOT be less than
  
    c_SECONDS_IN_DAY CONSTANT POSITIVE := ;
    c_HOURS_IN_DAY CONSTANT POSITIVE := ;
  
  
    c_UNIX_EPOCH_DATE_TIME_STRING CONSTANT VARCHAR() := // ::;
    c_DATE_FORMAT CONSTANT VARCHAR() := MM/DD/YYYY HH:MI:SS;
  
    VARIABLES
  
    v_sql_code PLS_INTEGER;
    v_sql_error_message VARCHAR();
  
    The variables have to be of NUMBER type because a PLS_INTEGER divided by
    another PLS_INTEGER can result in a fraction thus the result must be of
    NUMBER type only
  
    v_unix_seconds NUMBER;
    v_gmt_time_gap NUMBER;
  
    v_return_value DATE;
  
  BEGIN
  
    IF(NOT (p_num_hr_gmt_diff < (c_HOURS_IN_DAY)) AND
     NOT (p_num_hr_gmt_diff > (c_HOURS_IN_DAY))) THEN
  
     v_unix_seconds := (p_unix_date / c_SECONDS_IN_DAY);
     v_gmt_time_gap := (p_num_hr_gmt_diff / c_HOURS_IN_DAY);
  
     v_return_value := TO_DATE(c_UNIX_EPOCH_DATE_TIME_STRING c_DATE_FORMAT) +
              v_unix_seconds + v_gmt_time_gap;
    ELSE
     v_return_value := NULL;
    END IF;
  
    RETURN(v_return_value);
  
  EXCEPTION
  
    WHEN OTHERS THEN
     v_sql_code := SQLCODE;
     v_sql_error_message := SQLERRM(v_sql_code);
  
     DBMS_OUTPUTPUT_LINE(OTHER ERROR);
     DBMS_OUTPUTPUT_LINE(v_sql_error_message);
  
  END Unix_To_Oracle_Date;
  

From:http://tw.wingwit.com/Article/program/Oracle/201311/16933.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.