How can I change the CLINICALDATE from Centricity document into a date?

Question

Currently the date from this field is some number based on time since some date in the past. 

Answer 1

I find it much easier to use the Centricity built in function within Oracle to convert clinicaldate to date. It looks like this:

 

select ml.convert_id_to_date(document.clinicaldate) clndate from document

 

Likewise if you are trying to limit results to documents within a certain date you would use:

For the last 30 days:

select * from document where clinicaldate >= ml.convert_date_to_id(SYSDATE -30)

Since a specific date:

select * from document where clinicaldate >= ml.convert_date_to_id(to_date('2016-01-15','YYYY-MM-DD'))

or

select * from document where clinicaldate >= ml.convert_char_to_id('2016-01-15','YYYY-MM-DD')

 

EDIT: Fixed a typo in the spelling of the fuction. Also added convert_char_to_id example.

 

Answer 2

In CPS use the following mssql statement:

select DATEADD(day,clinicaldate/1000000/3600/24,'01-01-1960') from document

 

or EMR Oracle use:

select to_char(to_date('01-01-1960','mm dd yyyy')  + (substr(d.clinicaldate,1,10)/3600/24), 'MM/DD/YYYY') from document

Answer 3

You can put the following code in a Published Function and call it from anyone of your channels.

Description:

Convert date/time portion of the Centricity ID into a string.  Returns the date in the format yyyyMMddHHmmss (ie. 20120809171933).

Code:

function centricityIDtoDateString(id) {

   // extract the number of seconds since January 1, 1960 from the Centricity ID (first 10 characters)

   var secondsSince1960 = Number((id + '').substring(0, 10));

   

   // change to the number of seconds since January 1, 1970

   var secondsSince1970 = secondsSince1960 - 315619200;

   

   // create Java date object

   var date = new java.util.Date( secondsSince1970 * 1000 );

   

   // create SimpleDateFormat for GMT time zone

   var dateFormat = new java.text.SimpleDateFormat('yyyyMMddHHmmss');

   var gmtTimeZone = new java.util.TimeZone.getTimeZone('GMT');

   dateFormat.setTimeZone(gmtTimeZone);

   

   // return the formatted date

   return dateFormat.format(date);

}