Sidebar

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

0 votes
2.5K views
asked Mar 24, 2015 by mike-r-7535 (13,650 points)

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

3 Answers

+1 vote
 
Best answer

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.

 
answered Jun 17, 2016 by ryan-w-6043 (460 points)
edited Sep 26, 2016 by ryan-w-6043
+1 vote

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
answered Mar 24, 2015 by mike-r-7535 (13,650 points)
0 votes

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);
}
 
answered Mar 24, 2015 by mike-r-7535 (13,650 points)
...