Error when converting time from a string

aostropolets · October 30, 2020, 8:32pm

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)

Platform:
x86_64-w64-mingw32

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!

schuemie · October 31, 2020, 5:19am

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

aostropolets · October 31, 2020, 6:17am

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

schuemie · November 2, 2020, 5:23am

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).

aostropolets · November 2, 2020, 5:57am

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

schuemie · November 2, 2020, 10:56am

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

jposada · November 3, 2020, 9:47pm

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

github.com/OHDSI/SqlRender

CAST(@a as DATE) not working on BQ for YYYYMMDD string dates. Cohort Method package uses this format

opened 04:40AM - 13 Mar 20 UTC

closed 09:48PM - 03 Nov 20 UTC

jdposada

One of the parameterized SQL of the cohort method does this ```sql AND cohor…t_start_date >= CAST('@study_start_date' AS DATE) ``` the bigquery translation is attempting to do this ```sql cohort_start_date >= cast('20050101' as date) ``` However, according to BQ docs [here](https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#casting) > Casting from a date type to a string is independent of time zone and is of the form YYYY-MM-DD. I believe the issue may be solved modifying the csv file here `inst/csv/replacementPatterns.csv` with: ```sql IF (REGEXP_CONTAINS(CAST(@a AS STRING), r'[0-9]{8}.*'), PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(CAST(@a AS STRING),r'[0-9]{8}')), IF (REGEXP_CONTAINS(CAST(@a AS STRING), r'[0-9]{4}-[0-9]{2}-[0-9]{2}.*'), PARSE_DATE('%Y-%m-%d', REGEXP_EXTRACT(CAST(@a AS STRING),r'[0-9]{4}-[0-9]{2}-[0-9]{2}')), PARSE_DATE('%Y/%m/%d', REGEXP_EXTRACT(CAST(@a AS STRING),r'[0-9]{4}.{1}[0-9]{2}.{1}[0-9]{2}')))) ``` I did the following unit test with the following DrugExposureLongTerm ```R sql = "SELECT CAST('20100112' AS DATE);SELECT CAST('2010/01/12' AS DATE);SELECT CAST('2010-01-12' AS DATE)" print(translate(sql=sql, 'bigquery')) ``` ```SQL SELECT IF (REGEXP_CONTAINS('20100112', r'[0-9]{8}'), PARSE_DATE('%Y%m%d', '20100112' ), IF (REGEXP_CONTAINS('20100112', r'[0-9]{4}-[0-9]{2}-[0-9]{2}'), PARSE_DATE('%Y-%m-%d', '20100112' ), PARSE_DATE('%Y/%m/%d', '20100112' ))); SELECT IF (REGEXP_CONTAINS('2010/01/12', r'[0-9]{8}'), PARSE_DATE('%Y%m%d', '2010/01/12' ), IF (REGEXP_CONTAINS('2010/01/12', r'[0-9]{4}-[0-9]{2}-[0-9]{2}'), PARSE_DATE('%Y-%m-%d', '2010/01/12' ), PARSE_DATE('%Y/%m/%d', '2010/01/12' ))); SELECT IF (REGEXP_CONTAINS('2010-01-12', r'[0-9]{8}'), PARSE_DATE('%Y%m%d', '2010-01-12' ), IF (REGEXP_CONTAINS('2010-01-12', r'[0-9]{4}-[0-9]{2}-[0-9]{2}'), PARSE_DATE('%Y-%m-%d', '2010-01-12' ), PARSE_DATE('%Y/%m/%d', '2010-01-12' ))) ``` @pavgra

aostropolets · November 4, 2020, 12:22am

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)

schuemie · November 5, 2020, 8:54am

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

schuemie · November 23, 2020, 8:38am

This patch fix has now been released.