I'm using a find function to pull the clinicaldate of an EKG doctype.
Anyone know the MEL function to convert this clinicaldate string into an actual date?
Use this -
{fn timetoDate(var){
local hold = var/1000000
hold = val(sub(str(hold),1,match(str(hold),".") - 1))
hold = hold/86400
hold = val(sub(str(hold),1,match(str(hold),".") - 1))
return ADDDATES("01/01/1960","0","0",str(hold))
}
}
Basically remove the last 6 digits because its a workstation id (or so it says in the documentation) and you are left with seconds from January 1, 1960. MEL has some funky rounding or some overflow issues or something which is why I truncated the decimals. If you tried to use this to get a date time it would not be correct, but for a date it works.
In sql you can verify this using the built in function - SELECT TOP 1 dbo.Convert_ID_to_date(PID) FROM PatientProfile. You can also read more about it in the data dictionary available on the support portal, from the main page click on Formulas, its right at the top.
{ADDDATES("01/01/1960","0","0",str(div(find("FilteredDocuments","ClinicalDate","DocType","28")/1000000/3600,24)))}