BusinessObjects Board

Interpreting repo date fields in DB2 (FAQ addition?)

security Supervisor (4.x - 6.x)
#1

The FAQ includes some Oracle SQL for decoding BusObj Repo Date fields, here is a similar tip for DB2 :slight_smile:

:arrow_forward: Define a UDF such as

CREATE FUNCTION TIMESTAMP_FROM_UTC                                
(UTC INTEGER)                                                          
RETURNS  SYSIBM.TIMESTAMP                                              
SPECIFIC TIMESTAMP_FROM_UTC                                       
       LANGUAGE SQL                                                    
       DETERMINISTIC                                                   
       CALLED ON NULL INPUT                                            
       CONTAINS SQL                                                    
       NO EXTERNAL ACTION                                              
RETURN                                                                 
TIMESTAMP('1970-12-14-23.59.59.000000') + UTC SECONDS                  
                                                                      ;

which is then simply called in your SQL query eg

SELECT TIMESTAMP_FROM_UTC(M_ACTOR_N_LAD)
FROM OBJ_M_ACTOR

:arrow_forward: If you can’t use UDFs, or want a simpler way, use the following in your SQL query:

TIMESTAMP('1970-12-14-23.59.59.000000')
+ REPO_TABLE_COLUMN SECONDS             
+ CURRENT TIMEZONE

or

DATE(TIMESTAMP('1970-12-14-23.59.59.000000')
+ REPO_TABLE_COLUMN SECONDS             
+ CURRENT TIMEZONE)

or

TIME(TIMESTAMP('1970-12-14-23.59.59.000000')
+ REPO_TABLE_COLUMN SECONDS             
+ CURRENT TIMEZONE)

(where REPO_TABLE_COLUMN is the column, eg. M_ACTOR_N_LAD etc)

RobinM :uk: (BOB member since 2003-02-25)