OHDSI Home | Forums | Wiki | Github

SQL Server 2014 conversion issues with DateTime2

Implementers
#1

As I am implementing OMOP v6, I noticed that all of the DateTime types have been upgraded to DateTime2. I am hesitant to use DateTime2 as I have proven in the past (locally) that converting both ways between Unix time with milliseconds to DateTime2 can cause errors. Unix to DateTime had a loss of precision, but no errors. Unfortunately, this was with proprietary code, so I cannot share it here.

My question is if I change the OMOP CDM sql server ddl.txt script to replace DateTime2 with DateTime, will this cause any issues with any of the downstream tools or queries?

#2

I’m not sure about the implications of changing the datetime2 to datetime, but MS recommends using DateTime2.

I understand that you’ve experienced problems with interop with unix times, but should we go against the vendor recommendation because of your use case?

#3

I believe you stick with datetime2 for a variety of reasons.

  • If you have some evidence that explains why DT2 is bad for the community bring it forward and share. It is certainly welcomed and will be reviewed. There is precision lost when you back out of Datetime2 to Datetime so it would be a hard sell to the community. It is often easy to find local solutions but it is better to collaborate within the community to improve the model and tools which ultimately helps everyone (including you!)

David

#4

I am sorry, let me clarify; I am not asking for a change to be made to the type in OMOP, I am asking if I do it on my local instance if it will cause any issues with any of the tools.

We are part of AOU grant for FQHCs, so we are already limited to using some of the old schema(Death table for example). I am looking to use as much of the OMOP v6 as possible to keep from having to rework as much code in the future when AOU moves to v6 (or in case we decide to stand up a full OMOP server).

The output, for the purposes of AOU upload, will be in text anyway, so the output between DateTime and DateTime2 will be the same.

I am sorry for the confusion, I should have placed the caveats in my question.

#5

I don’t think you’ll have any problems provided all the columns are changed in your local env from dateTime2 to DateTime. My hypothosis is that we only use those fields for inter-record type of compariisons (colA < col b or datediff(cola, colb)) and provided both of those are the same underlying type you should get consistent results.

But, your mileage may vary. If you do anything make sure you have good test cases to ensure proper output.

-Chris

1 Like
t