OHDSI Home | Forums | Wiki | Github

Error when converting time from a string

(Anna Ostropolets) #1

I saw so many new releases of R tools that I’ve been tempted to upgrade my R to v4 and all the packages and tested them out.
I tried running a cohort study generated by Atlas + hydrated and got the following error when executing runCmAnalyses:

“Error executing SQL:ncom.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string”,

specifically here:
AND cohort_start_date >= CAST(‘20100100’ AS DATE)

More info:
R version 4.0.3 (2020-10-10)


Other attached packages:

  • CohortMethod (4.0.0)
  • FeatureExtraction (3.1.0)
  • Andromeda (0.3.2)
  • dplyr (1.0.2)
  • Cyclops (3.0.0)
  • DatabaseConnector (3.0.0)
  • snow (0.4-3)

I imagine there is something silly I haven’t done but can’t figure out what. Any help will be much appreciated! :slight_smile:

(Martijn Schuemie) #2

That date string is invalid. Did you specify a study start date? Could you share the specifications?

(Anna Ostropolets) #3

Yes, I actually specified it (although it’s not critical so can be removed). Specification is here: http://atlas-demo.ohdsi.org/#/estimation/cca/412
Didn’t even notice that sql had 2010-01-00, but the date in atlas was def Jan 01

(Martijn Schuemie) #4

That is weird. ATLAS generates the correct string, and there’s no place downstream where the string could be changed. In fact, I ran your study locally and it works fine for me (except I get a ‘high correlation’ error down the road).

(Anna Ostropolets) #5

That’s strange indeed. Let me try to chose a different date/ remove the date and rerun it. Thank for looking into this!

(Martijn Schuemie) #6

Could you check the value for studyStartDate in cmAnalysisList.json in the inst/settings folder of the package you generated?

(Jose Posada) #7

Will this be related with an issue I reported here?
@aostropolets @schuemie

(Anna Ostropolets) #8

What I actually see is that Atlas specification is fine. But when I hydrate the package and open the specification of the package, start date gets converted into 2.01001E7. In fact, similar issues are present for excluded concept ids (e.g. 4.627619E7)

(Martijn Schuemie) #9

Oops. I see that when you call hydrate() when specifying the package name it converts the JSON to R objects and back to insert the name, and that introduces scientific notation. (I had set the package name in the JSON file when I tested it, which is why I didn’t run into this bug).

I made a fix in the develop branch. Could you give it a try? Use

remotes::install_github("ohdsi/Hydra", ref = "develop")

to install

(Martijn Schuemie) #10

This patch fix has now been released.