Converting between dates and IDs in CPS/EMR

Published on by tygh

Occasionally, you need to retrieve date fields that don’t look like dates. The most common example in CPS is the clinicaldate field in the document table.

There are formulae for converting between these IDs and their corresponding dates.

Here is an example query that shows how to retrieve the clinicaldate as a real date as well as showing how to convert the DB_CREATE_DATE to an ID format.

dbo.Convert_ID_to_date() and dbo.Convert_Date_to_ID()


SELECT ClinicalDate, dbo.Convert_ID_to_date(clinicaldate) theDate,
DB_CREATE_DATE, dbo.Convert_Date_to_ID(DB_CREATE_DATE) theID
FROM document
WHERE PID = 1225794115001060;