Dealing with Meditech Dates (including Clinical formats) in SQL (Meditech, iPeople Echo, SQL Server)

For those regular followers of this blog who don’t realize it,  I work in healthcare.

While I usually don’t post industry specific things here,  I’m going to start as one of the purposes of this blog is to keep track of things I learn,  that I’ll probably forget as I move onto the next project.

Those of you are are familiar with Meditech know that there are many instances where dates are stored in a “Clinical Date” format.   That is,  the number of days since 3/1/1980.

We recently started using iPeople Echo,  which we purchased as part of their Connect suite, to copy data from Meditech’s proprietary Dictionaries (the Meditech term for tables) into SQL server.

When querying the data,  most of the dates were in the Meditech standard for Canadian sites (YYYYMMDD).

When writing a sql query that involves dates,  its often necessary to convert the Meditech date to a SQL datetime datatype.

The CONVERT function does this,  the trick is knowing the correct format.

Here’s an example where it converts the ADMITDATE to a SQL date type.


The parameters are

CONVERT([data-type(date)],database field,format)

The format 112 indicates  YYYYMMDD

Here is a reference on format codes. 

The Meditech clinical date format is trickier to deal with as it represents the number of days passed since 3/1/1980.

If you’re writing a query that requires you to handle clinical dates,  its best to use a Stored Procedure so that you can do the conversion once,  and use the results in your actual query.  I’ll post a live example below,  but for now,  here’s the base query.

CAST (DATEDIFF(day,'3/1/1980',CONVERT(date,@startdate,112)) as int)

In order to use the result of this statement in a WHERE clause pulling from iPeople DR,  it needs to be converted to a number.

As you’ll see in this partial example row from our Radiology Exam Information table,  the field ‘EXAMDATES’ is a number. (and, for some reason, a plural)

D10027 9285 1 802279697 RS4X000115 NULL NULL

When looking for a range of dates,  user’s prefer to be able to enter a date range that makes sense to them,  its up to as, as programmers to accommodate this.   (and to make our own lives easier while testing queries)

If you use the above code fragment to convert the examdate field above your code would look like this


The result would be  02 Aug 2005,  or using the 112 format 20050802

I mentioned stored procedures earlier and will post an example,  but it will require some explanation.

The following stored procedure was written for a project that gives users geographic information (primarily driving distance to each of our facilities) for patients who presented for certain types of radiology exams.

The information is going to be used to locate new equipment and tweak service delivery.

Seeing as I need to query the radiology exam table used as an example above,  I wanted an easy way to set date ranges without doing a lot of work in the PHP script that calls this stored procedure.

The stored procedure runs on another SQL Server instance,  with the iPeople Data server added as a linked object.  You’ll see in the query how I reference those tables.

I do this so I can write stored procedures while not affected the actual iPeople database,  and, so I can use custom tables to contain select data (so I don’t have to change the query if exam codes change),  as well as store geographic data such as lat and long for each postal code.

You’ll also see, that I use the Meditech date format YYYYMMDD  as the procedure parameters (@startdate,@enddate) and then convert them to the clinical date format (@from and @end)  prior to the select running.

The @from and @end variables are used in the WHERE select to limit the results to the desired date range.

ALTER PROCEDURE [dbo].[radpatientsforgis]
@startdate varchar(8),
@enddate varchar(8)

DECLARE @from int
DECLARE @end int

SET @from = CAST (DATEDIFF(day,'3/1/1980',CONVERT(date,@startdate,112)) as int)
SET @end = CAST(DATEDIFF(day,'3/1/1980',CONVERT(date,@enddate,112)) as int)

--b.NAME as name,
a.EXAMACCOUNT as account,
a.EXAMNUMBER as number,
REPLACE(b.ZIP,' ','') as zip, as lat,
c.long as lon,
CONVERT(varchar(12),DATEADD(DAY,CONVERT(int,a.EXAMDATES),'3/1/1980'),106) as dates
IPEOPLEDATA.IPEOPLE_DR.Rad.RadiologyExamInformation as a
geolocation as c ON REPLACE(b.ZIP,' ','') = REPLACE(c.code,' ','')
radexamcodes as d ON d.exmnemonic = a.EXAMMNEMONIC
a.EXAMDATES >= @from
a.EXAMDATES <= @end
AND = 1

A sample result is below with altered data

account exam number zip lat lon dates
XXXXXX MAMSCBIL xxxxx LNLNLN 83.3691 -64.8376 04-Mar-15

Note: I did not receive any consideration from iPeople for this post, or the link.  I have no issues with letting people know about products and applications that make my life easier.